Showing results for 
Search instead for 
Did you mean: 

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

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

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?
Labels (1)
3 Replies

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?

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

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