Filters with Subqueries

Renato Marcello dos Reis shared this question 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

The same question

Comments (1)

Simon ● 48 days ago

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

Reply URL

Renato Marcello dos Reis ● 48 days ago

Hi Simon,

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

Regards,

Renato Marcello

URL

Simon ● 45 days ago

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

URL