Limiting Subquery results - how?

Wesley Tamkin shared this question 4 months ago
Answered

Our implementation of Yellowfin is integrated into a case management software known as I-Sight. The case management software has a table for cases and many tables that connect to that case ID.

I find myself wanting to have a case level report but with the most recent datapoints from some of these other tables. For example, I want to see the case number etc, but I also want to see the most recent note.

I have tried to do this a few ways but none of them work. The simplest way I would think would be to have a Query for the case level data and then a subquery that has a filter and returns the data from MAX date only. However when I do this, even though the filter is in the subquery, the date it is filtered to is from the master. Example, I have 10 cases and one of them has a note from today and no other one does. Because today's note is the most recent that case will show today's note; which is as expected but all other cases will show blank because they have no note from today. The expected result is that each case would show a note with the respective max date on the note for that case.


Thoughts?

I'm not sure which version of yellowfin it is, I can't seem to find it in our integration. I can ask the vendor though if that seems important.

Comments (2)

photo
1

Turns out version is 7.4.14, don't see how to update the post though.

photo
1

Hi Wesley,


Thanks for the clear info, and don't worry about the version, I have updated it for you.


Now in terms of what you're facing, a standard append sub-query should allow all results from the master query to be returned, and by applying a filter only to the sub-query will allow you to only limit that result set.

The only thing here to keep in mind is the max date aggregation should only be applied on the sub-query. That way the master query returns all cases , but the sub-query only returns the single row for the case. Just ensure the queries are linked by CaseID only, and that there are no filters on the master query.


Outside of this what I can suggest is to reach out to your YF vendor and outline your use-case, as they would understand the ins and outs of the data set and be able to provide you with a report template, or instructions on how to build such a report. Also keep in mind Yellowfin 7.4 has been out of support for a few years now so there is little more I can do here. If for whatever reason you're not getting the expected level of support from your vendor, just create a private ticket and I can see what more I can do for you here.


Hope this helps!


Regards,

David

photo
1

I appreciate it. I did try that already but just to make sure something else wasn't going on I made a new report with no data but case ID. I ran into the same problem where I am getting all of the master records but the sub query record only returns a result for the latest record period; not just for that case. I'll submit a ticket to my vendor.

photo
1

Let me know how it all goes Wesley, and please feel free to raise a private ticket if you would like to share feedback.

Regards,

David

photo