Flag Yes/No for Last 5 month and field Value

[karan_kn]

Please help me to flag the table as per the below condition

Last 6 Months and Rank = A & B Flag Yes else No

[neelamsaroha157]

Is this what you are looking for..

[sergio0592 · Accepted solution]

If it's last 5 months from today, you can use :

LOAD *,
if(date#(Year(Date)*10&num(month(Date)),'YYYYMM') >= Date#(AddMonths(date#(Year(Today())*10&num(month(Today())),'YYYYMM'),-6),'M/D/YYYY') and (Rank='A' or Rank='B'),1,0) as FL

INLINE [
    Date, Rank, ID
    1/1/2016, A, 1
    2/1/2016, B, 2
    3/1/2016, C, 3
    5/1/2016, E, 4
    6/1/2016, E, 5
    7/1/2016, D, 6
    8/1/2016, A, 7
    9/1/2016, B, 8
    10/1/2016, E, 9
    11/1/2016, A, 10
    12/1/2016, C, 11
    1/1/2017, C, 12
    2/1/2017, D, 13
    3/1/2017, E, 14
    4/1/2017, E, 15
    5/1/2017, D, 16
    6/1/2017, A, 17
    7/1/2017, B, 18
    8/1/2017, E, 19
    9/1/2017, A, 20
    10/1/2017, C, 21
    11/1/2017, C, 22
    12/1/2017, D, 23
    1/1/2018, E, 24
    2/1/2018, E, 25
    3/1/2018, D, 26
    4/1/2018, A, 27
    5/1/2018, B, 28
    6/1/2018, E, 29
    7/1/2018, A, 30
];

[karan_kn]

Nope,

Yes Means Rank=A, B and Last 5 month

No Means Exclude above and show rest of the values

[neelamsaroha157]

Check this.

I have added last 6 months, you can change it to 5 months.

[karan_kn]

If I click No, it showing everything instead of C,D,E in Rank

[neelamsaroha157]

Check this. I am not sure how you would like to treat the nulls.

[MK9885]

Try below script?

Hope that helps....?

Table1:

//PrecedingLoad//

load*,

if(Rolling6='1' and NewRanks='Yes','Yes' ,

if(Rolling6='0'and NewRanks='No','No')) as RankFlag;

//PrecedingLoad//

load*,

If( Date > monthstart(addmonths(today(),-5)) and Date <= today(),1,0 ) as Rolling6,

if(Rank='A','Yes',

if(Rank='B','Yes','No')) as NewRanks,

Year(Date) as RankYear,

Month(Date) as RankMonth;

//OriginalLoad//

LOAD * INLINE [

    Date, Rank, ID

    1/1/2016, A, 1

    2/1/2016, B, 2

    3/1/2016, C, 3

    5/1/2016, E, 4

    6/1/2016, E, 5

    7/1/2016, D, 6

    8/1/2016, A, 7

    9/1/2016, B, 8

    10/1/2016, E, 9

    11/1/2016, A, 10

    12/1/2016, C, 11

    1/1/2017, C, 12

    2/1/2017, D, 13

    3/1/2017, E, 14

    4/1/2017, E, 15

    5/1/2017, D, 16

    6/1/2017, A, 17

    7/1/2017, B, 18

    8/1/2017, E, 19

    9/1/2017, A, 20

    10/1/2017, C, 21

    11/1/2017, C, 22

    12/1/2017, D, 23

    1/1/2018, E, 24

    2/1/2018, E, 25

    3/1/2018, D, 26

    4/1/2018, A, 27

    5/1/2018, B, 28

    6/1/2018, E, 29

    7/1/2018, A, 30

];