Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data, say for example the following fields:
Client ID
Date of Purchase (This is a time stamp with also hour, mins and seconds)
Category
Store
Region
A client can have multiple purchases on the same day , i.e. repeated client ID on a date, but I would like extract a list of the customer's last purchase using First Sorted Value. Can someone help me on what the code would look like.
Thanks
H
Load ClientID,
FirstSortedValue(DateofPurchase ,-DateofPurchase ) As DateofPurchase ,
FirstSortedValue(Category,-DateofPurchase ) As Category,
FirstSortedValue(Store ,-DateofPurchase ) As Store,
FirstSortedValue(Region,-DateofPurchase ) As Region
Group by ClientID;
Load ClientID, Timestamp#(DateofPurchase,'M/D/YYYY hh:mm:ss') As DateofPurchase ,Category,Store ,Region
From Table
Load ClientID,
FirstSortedValue(DateofPurchase ,-DateofPurchase ) As DateofPurchase ,
FirstSortedValue(Category,-DateofPurchase ) As Category,
FirstSortedValue(Store ,-DateofPurchase ) As Store,
FirstSortedValue(Region,-DateofPurchase ) As Region
Group by ClientID;
Load ClientID, Timestamp#(DateofPurchase,'M/D/YYYY hh:mm:ss') As DateofPurchase ,Category,Store ,Region
From Table
Thank you Anbu