Filters with Subqueries

Renato Marcello dos Reis shared this question 48 days ago
Answered

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

Comments (1)

photo
1

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.


/79864d14727e8c6492082bb0c46f1829


Let me know how you go.


Kind regards ,

Simon

photo
1

Hi Simon,

Thanks a lot, that's exactly what I was trying to achieve.

Regards,

Renato Marcello

photo
1

Hi Renato,


Not a problem, I'm glad I could help.


I've gone ahead and marked this as answered, please feel free to respond here if you have any further questions.


Kind regards,

Simon

photo