Filters with Subqueries
Hi guys,
I hope everything is fine with all of you.
I'm having a client who asked me something that I can do easily in SQL Query, but I couldn't find a way to do the same thing in Yellowfin.
To explain, I'm gonna put a query below, so I can explain what I'm trying to achieve.
SELECT A.PRIMARY_CODE, A.SALES_DATE, A.PRODUCT FROM SALES A WHERE SALES_DATE = (SELECT MAX(SALES_DATE) FROM SALES B WHERE A.PRIMARY_CODE = B.PRIMARY_CODE) GROUP BY A.PRIMARY_CODE, A.SALES_DATE, A.PRODUCT
The above code has a simple meaning. The client has sales from products more than once, and the query should give us only the most recent one. We just need to show the top 1 sale from which the date is the newer one.
I tried to achieve this by using the custom query over a filter, but the subquery cannot have a relation to the report columns, so I can't do some "WHERE A.PRIMARY_CODE = B.PRIMARY_CODE".
Is there a simple way to do this in Yellowfin?
Thanks in advance.
Regards,
Renato Marcello
Hi Renato,
Thanks for your question.
You should be able to do this with an Append Subquery. In the Master Subquery, you can add a field with Max(Date) aggregation to get the value of the most recent Sales. You can then join the Append Subquery on the Primary Key, and the Date where Max(Date) = Date. Then, dragging the relevant fields into the Subquery should get you the filtered results.
Let me know how you go.
Kind regards ,
Simon
Comments have been locked on this page!