›

Help us build a better product, earn the PaxPro Advocate badge: Six short questions, one cool badge.
Visit the official Paxata Documentation portal for all of your doc needs.

How do I implement Partition-Rank-Filter based on Rank operation in Paxata?

MagmaManMagmaMan Posts: 15 ✭
in Data Prep Q&A
I have a sales data that gives latest aggregated sales every month.
When it deposits data for month of May, it also has latest details for month of Jan, Feb, Mar and Apr too.
The idea is to select the value specified in the latest drop.
So, I want to partition by product-code, rank based on drop-time and then get the latest one.
How do I do this Operation in Paxata?

Answers

  • MagmaManMagmaMan Posts: 15 ✭
    I see there is a GroupBy which can be coupled with "First", "Last" functions. But where do I get to specify the sort order for the first and last functions?
  • MagmaManMagmaMan Posts: 15 ✭
    I manually checked and found that the "first" and "last" are given by the natural order in which the dates exist in the data and nothing more. So, definitely, there is no provision for a sort-order for this operation - which is a bit letdown for me.... Hmmmmm....
  • marthamiller_SEmarthamiller_SE Posts: 11 mod
    We spoke offline but I wanted to respond for the benefit of the community.  You can perform an operation similar to partition and rank using 2 steps in Paxata.  First, perform a group by on the dataset at the store and location level with the aggregate column as the max(date).  This will give you the latest date with sales.  Next perform a lookup to bring the original dataset back into the project selecting the the "reverse" option under Options/lookup direction.  This will give you the store sales information joined on only the max date as desired.  After the lookup you can hide extraneous columns using the "Columns" tool in the left hand toolbar
Sign In or Register to comment.