Indicate date field that greater to max date minus 1 month

[RutiTaumanRubin]

Hi All

I want to create new field in the script that will populate with value 1 if the date is greater that max(date) minus 1 month.

I try the following but its not working:

IF(MAX(Addmonths(date(date#(ACTION_DATE,'YYYYMMDD')),-1))>=date(date#(ACTION_DATE,'YYYYMMDD')),1,0)

Could you please advise?

Thanks!!

[sunny_talwar · Accepted solution]

Max date will have to come from a resident load or a mapping load... it can't be used in the non-aggregated table... do something like this

MainTable:
LOAD Date(Date#(ACTION_DATE,'YYYYMMDD')) as ACTION_DATE, other_fields
FROM ...;

Left Join (MainTable)
LOAD Max(ACTION_DATE) as MaxDate
Resident MainTable;

FinalTable:
LOAD *, If(AddMonths(MaxDate, -1) > ACTION_DATE, 1, 0) as Flag
Resident MainTable;

DROP Table MainTable;