Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RutiTaumanRubin
Creator
Creator

Indicate date field that greater to max date minus 1 month

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

1 Reply
sunny_talwar

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;