BMC - Is it possible to exclude some data using sub-query

Hello Bharat,

Try to use the filter within sub-query and exclude the required values.

See if this helps.

Regards,

Abhay

Hi Abhay,

Thanks for reaching out. This is going to depend on how your Joins are setup and where the field you're looking to exclude data is placed.

Here are my unfiltered results in my sub-query:

I just have Athlete Region = Athlete Region set for my Join.

Now, like in your use case, I basically want to exclude a couple results from my second column, in this case I'll remove 'EUR', 'JPY', 'USD' from my Currency Code column by adding the field as a filter and explicitly defining the values of 'AUD' and 'CAD' in list:

These are now the results:

If you're still seeing all your results in your second column after filtering them in the sub-query, I'd suspect that would be the case because your field is in your Master Query instead of in your Sub-Query.

As you can see below, if I remove Currency Code from the sub-query and place it into the master query instead, it no longer filters the results, though you can see the sub-query filter is still in the report:

As such, can you check and confirm your 'Relation Type' field and relevant filter are both found in your sub-query and not the master query?

Regards,

Mike

Regards,

Mike

Thanks Mike for the information.

The question if from Bharath and I was sharing some inputs. This information will surely help.

Regards, Abhay

Hi Abhay,

No problem. Please let me know if you require any further assistance with this.

Regards,

Mike

Regards,

Mike

Hello,

This is not the exact use case I am trying to do. In your case, all the regions have the same currency code.

For example lets assume some regions have different currency codes. Region Asia has both AUD and JPY. I want to hide regions which has currency code as JPY. In that case Region Asia should not appear in my results even though it has other currency codes.

I made a spreadsheet as per your data and an expected output column.

Please let us know if this is possible.

I tried many ways but I cannot achieve expected results.

Regards,

Bharath

Hi Bharath,

I spent some time trying to figure this out, but I'm struggling with this as well. That said, this certainly doesn't seem to be defective behavior, but a matter of how to setup the report. As I'm sure you're aware, Report Creation does fall more in the realm of Consulting services, and they'd definitely be better equipped to assist with something like this. It seems to be a simple enough use case, but also appears to be deceivingly difficult in execution. I reached out to one of our Consultants to see if they can point me in the right direction/offer any tips, but I'm awaiting feedback and technically Support isn't the correct channel for this time of question, but I'll see if I can dig up any extra info or pointers.

Regards,

Mike

Hi Mike,

Yes,I understand this is something consultants can help. But in this case, we tried all possible options like calculated fields, link filters from sub query , but none of them helped.

So reached to you to confirm if this is product limitation or a defect. If you can confirm that, we will look into the other alternate approach.

Regards,

Bharath

Hi Bharath,

As long as this type of manipulation is possible via SQL, it should be possible in Yellowfin. The first thing I'm trying to determine here is whether this is possible via SQL. If you can provide a SQL statement that gets your initial data set to your desired data set, I can work from there to come up with a solution within YF, but it's the actual SQL/data manipulation itself that is the part I'm unsure of.

Regards,

Mike

Hi Mike,

Ok, please let us know if it is possible via direct sql. Attached sql's from other reporting solution which gets data as expected.

Regards,

Bharath

Hi Bharath,

Thanks for your response. To address your previous response, there's doesn't appear to be a defect here - this is just a matter of actually building the report according to the desired specification. That said, based on the provided SQL, this is a considerably more complicated setup than Support is equipped to handle.

The only recommendations I can make at this point are to either:

1. Attempt running the supplied query in a Freehand SQL Report,

2. Attempt utilizing your WHERE clause as part of a Freehand SQL Calculated Field. For example:

HPD_STATUS.STATUS  NOT IN  ('Closed','Cancelled') AND HPD_HELP_DESK.ASSIGNED_GROUP IN ('IN_GGN_ROC_AA_DATA') AND INC_RELATIONSHIP_TYPE.VALUE IN ('Caused','Caused by','Duplicate of');

Hopefully this helps.

Regards,

Mike

Hi Mike,

I understand this something support cannot handle. I tried the same condition in free hand sql, but it is not working as the same. It is just filtering the data based on filter values passed. I see this as application limitation, coz we tried all possible ways.

Regards,

Bharath

Hi Bharath,

Thanks for your response. I'm afraid we in Support don't really have the report-building expertise to confirm whether this is an actual limitation or not, but what I can say is this falls outside the realm of Support and that we've exhausted the possibilities here from a Support perspective. This being the case, I'm going to go ahead and close this ticket out.

If you'd be interested in discussing this with a Consultant, please reach out to your Account Manager to discuss options, or let me know and I can reach out to him on your behalf. In addition, if anything I've said thus far requires any further clarification, if you respond, the ticket will re-open and be put it back in my queue and I'll be happy to provide further information.

Regards,

Mike

Regards,

Mike

Hi Mike,

Thank you for that, we are currently looking at manual options to extract data as required. I have some followup questions on this and whether we can raise an RFE to build such reports.

Can we connect over a call this week to discuss, wont take more than 5-10 mins.

Regards,

Bharath

Hi Bharath,

I've discussed this with one of our consultants, but he's doing on-site consulting work the next several days. Unfortunately, Support just won't be very helpful in this case, as it's far out of our wheelhouse.

Feedback from consulting thus far though is that "this is a bit tricky to accomplish", but it is possible. He will provide some thoughts on this as soon as he's able. I did note we've been discussing this for a while, so he'll try to provide some additional feedback before returning to the office in a few days. Hope this is okay.

Regards,

Mike

Hi Bharath,

I received some feedback on this... unfortunately, it's a bit of an inelegant solution, but it will get you to the result you're looking for.

Using the following example, I have this:

And what I want to happen is if the Region contains a Currency code of JPY to not display that Region at all, making the final result look like this:

To accomplish this you can do:

Main query: athlete region

append sub query: "inner join" on region = region

in the append, create a calc field that does a group_concat or string_agg (will need to use a pre-defined function), filter on group_agg(currency) does not contain 'JPY'.

Please give this a try and let me know how goes.

Regards,

Mike

Hi Bharath,

I just wanted to check in and see how things are going with this.

Regards,

Mike

Hi Bharath,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike