Grouping and Ranking based on Report Prompt in Incorta
My table CUSTOMER_TRX_MV has the below sample ,
INVOICE_RANK will partition the records based on customer# and order by invoice date
RANK ()OVER ( PARTITION BY CUSTOMER# ORDER BY invoice_date DESC) INVOICE_RANK
My report should display the records with the below conditions met based on report prompt "As_of_Date",
SELECT * FROM
(SELECT CUSTOMER# INVOICE_DT EFFECTIVE_FROM EFFECTIVE_TO INVOICE_RANK
where INVOICE_DATE <= As_of_date
and As_of_date between EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT
order by inv_rank)
WHERE RANK = 1 GROUP BY CUSTOMER#
So my report should details of each customer with max of invoice date <=As_of_date grouped by customer#.
Please let me know how to achieve this in Incorta.
This video shows how you can design this solution recommended by Amit:
Playlist for all the videos:
This solution in the video is working for most of cases, but i got some data sets where there are max dates are same for two records. in that case i want both the records to be pulled, but when i m applying the last version only one of the records getting pulled. is there is anyway to overcome this challenge.kindly refer to screenshot for the sample dataset.
Max date for customer is 7/1/20 which has two records contains different org codes,but after applying the last version filter only one record is getting pulled and appearing in the dashboard.