Compariosn between dates

[khadar]

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

[chris_djih]

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