Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Can anyone please help with the below scenario where need to derive the column status based on the comparison between the dates( current date and previous date).
For example we have to do the comparison for the country code change between 04/01 & 12/01. if the same country code present in the previous date then status will be Old other wise New.
Customer Id | Date | Country Code | Status |
10 | 04-01-2022 | AU | New |
10 | 04-01-2022 | IN | New |
10 | 04-01-2022 | NZ | New |
10 | 12-01-2022 | CN | New |
10 | 12-01-2022 | NZ | Old |
10 | 12-01-2022 | AG | New |
10 | 25-01-2022 | BP | New |
10 | 25-01-2022 | IP | New |
10 | 25-01-2022 | AG | Old |
You can sue the Previous function.
But to use it correctly you first need to order your table correclty (in oyur case by Date).
so your final code would look similar to this:
finaldata:
Load CustomerID,
CountryCode,
Date,
If(Previous(CustomerID) = CustomerID,
If(Previous(CountryCode) = CountryCode, 'Old', 'New')) as Status
resident rawdata
order by CustomerID, Date desc