0

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 
    FROM CUSTOMER_TRX_MV
    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.

Thank you.

Regards,

Nivetha

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • We can do this by creating a filter using the lastVersion function . So drag the customer number field in filter , select last version as the operator and drag the invoice date to the sort by field which opens up.

    Like
  • This video shows how you can design this solution recommended by Amit:

    - https://youtu.be/fI6KMmV2BnE

     

    Playlist for all the videos:

    https://www.youtube.com/playlist?list=PLhaCz3hHIgcINS0QQjj05JWOTOsmdXTb3

    Like
  • Hi,

    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.

    Like
Like Follow
  • 4 mths agoLast active
  • 3Replies
  • 94Views
  • 4 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!