Filters with Subqueries
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.