Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please help me to flag the table as per the below condition
Last 6 Months and Rank = A & B Flag Yes else No
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
];
Is this what you are looking for..
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
];
Nope,
Yes Means Rank=A, B and Last 5 month
No Means Exclude above and show rest of the values
Check this.
I have added last 6 months, you can change it to 5 months.
If I click No, it showing everything instead of C,D,E in Rank
Check this. I am not sure how you would like to treat the nulls.
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
];