Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please hep me to create new field using the below logic
(If Status - A or B
Date- Last 6 month? )
Flag - Yes or No
Ex: if(match(Status,'A', 'B') and Last 6 month ,'Yes','No') as Flag
LOAD * INLINE [
ID, Status, Date
1, A, 9/16/2017
2, B, 10/28/2017
3, C, 11/12/2017
4, A, 12/13/2017
5, C, 1/15/2018
6, D, 2/8/2018
7, B, 4/22/2018
8, D, 4/17/2018
9, B, 5/16/2018
10, A, 6/2/2018
];
Perhaps this?
Sample:
LOAD * INLINE [
ID, Status, Date
1, A, 9/16/2017
2, B, 10/28/2017
3, C, 11/12/2017
4, A, 12/13/2017
5, C, 1/15/2018
6, D, 2/8/2018
7, B, 4/22/2018
8, D, 4/17/2018
9, B, 5/16/2018
10, A, 6/2/2018
];
Max:
Load Max(Date) as MaxDate Resident Sample;
LET Var_Max = AddMonths(MaxDate, -6);
Drop Table Max;
Final:
NoConcatenate
Load ID, If(Date<='$(Var_Max)', 'Yes', 'No') as Flag, Date, Status From Sample;
Edited - Load ID, If(Match(Status,'A','B') and Date<='$(Var_Max)', 'Yes', 'No') as Flag, Date, Status From Sample;
Drop Table Sample;
Last 6 months from which date? Today?
Hi,
INPUT:
LOAD * INLINE [
ID, Status, Date
1, A, 9/16/2017
2, B, 10/28/2017
3, C, 11/12/2017
4, A, 12/13/2017
5, C, 1/15/2018
6, D, 2/8/2018
7, B, 4/22/2018
8, D, 4/17/2018
9, B, 5/16/2018
10, A, 6/2/2018
];
OUTPUT:
LOAD ID,Status,Date, if(match(Status,'A','B') and Date <= AddMonths(Today(), -6),'Yes','No') as Flag //Date < = AddMonths(Max(Date), -6)
resident INPUT;
DROP TABLE INPUT;
HTH
André Gomes
Today
May be this
If(Match(Status, 'A', 'B') and Date >= AddMonths(Today(), -6),'Yes','No') as Flag
or
If(Match(Status, 'A', 'B') and Date >= MonthStart(Today(), -6),'Yes','No') as Flag
Sample script used
LOAD *,
If(Match(Status, 'A', 'B') and Date >= AddMonths(Today(), -6),'Yes','No') as Flag1,
If(Match(Status, 'A', 'B') and Date >= MonthStart(Today(), -6),'Yes','No') as Flag2;
LOAD * INLINE [
ID, Status, Date
1, A, 9/16/2017
2, B, 10/28/2017
3, C, 11/12/2017
4, A, 12/13/2017
5, C, 1/15/2018
6, D, 2/8/2018
7, B, 4/22/2018
8, D, 4/17/2018
9, B, 5/16/2018
10, A, 6/2/2018
];
I tried the script, Date field s not correct.
Thanks for the response, but Date Flag is not correctly flagged
Why not? what is wrong?
Dec, April, May and June only appeared, Jan, Feb is missing. for last 6months data.
Can I use like this?
If(Match(Status, 'A', 'B') and Date >= AddMonths(Today(), -6) and Date<= Today(),'Yes','No') as Flag1