Flag field value and last 6 month

[karan_kn]

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

];

[Anil_Babu_Samineni]

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;

[sunny_talwar]

Last 6 months from which date? Today?

[agomes1971]

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

[karan_kn]

Today

[sunny_talwar]

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

];

[karan_kn]

I tried the script, Date field s not correct.

[karan_kn]

Thanks for the response, but Date Flag is not correctly flagged

[sunny_talwar]

Why not? what is wrong?

[karan_kn]

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