Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the data as shown here
Rep | Date | Activity | Leading_Flag | Trailing_Flag |
ABC | 1/1/2016 | 0 | 1 | 3 |
ABC | 1/2/2016 | 0 | 2 | 2 |
ABC | 1/3/2016 | 0 | 3 | 1 |
ABC | 1/4/2016 | 1 | 0 | 0 |
ABC | 1/5/2016 | 0 | 1 | 1 |
ABC | 1/6/2016 | 5 | 0 | 0 |
ABC | 1/7/2016 | 0 | 1 | 2 |
ABC | 1/8/2016 | 0 | 2 | 1 |
ABC | 1/9/2016 | 1 | 0 | 0 |
ABC | 1/10/2016 | 3 | 0 | 0 |
ABC | 1/11/2016 | 0 | 1 | 1 |
ABC | 1/12/2016 | 2 | 0 | 0 |
Rep, Date and Activity are my fields in the sample data.
I need to create a flag as mentioned in Leading_Flag and Trailing Flag.
If you consider date 1/1/2016, the Activity is 0, so the Leading_Flag should count it as 1
Consider date 1/2/2016, the Activity is 0, so the Leading_Flag should add first row and second row, so it should give 2
Consider date 1/3/2016, the Activity is 0, so the Leading_Flag should consider the consecutive 0 rows from top and give the count as 3.
If the Activity is having value other than 0, then Leading_Flag should have value 0.
Again, if you consider date 1/7/2016, Activity is 0, so the Leading_Flag should show as 1
Consider 1/8/2016 date, Activity is 0 (consecutive zero), so the Leading_Flag should show as 2.
Sameway, for getting Trailing_Flag.
If you consider date 1/1/2016, the Activity is 0, date 1/2/2016, the Activity is 0, date 1/3/2016, the Activity is 0, there are 3 consecutive zeros, so the Trailing_Flag should show the count as 3 for first row.
Consider date 1/2/2016, the Activity is 0, date 1/2/2016, the Activity is 0, there are 2 consecutive 0's from second row (Second row and third row), so the Trailing_Flag will have value 2.
Can someone please help me to achieve this flag fields?
Thanks in advance.
try this
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
aa:
LOAD * INLINE [
Rep, Date, Activity
ABC, 1/1/2016, 0
ABC, 1/2/2016, 0
ABC, 1/3/2016, 0
ABC, 1/4/2016, 1
ABC, 1/5/2016, 0
ABC, 1/6/2016, 5
ABC, 1/7/2016, 0
ABC, 1/8/2016, 0
ABC, 1/9/2016, 1
ABC, 1/10/2016, 3
ABC, 1/11/2016, 0
ABC, 1/12/2016, 2
];
NoConcatenate
bb:
load *,
if(number=1,if(Activity=0, 1,0), if(Activity=0,peek(Previous('Leading_Flag'))+1,0)
) as Leading_Flag;
load *,recno() as number
resident aa order by Rep,Date;
drop table aa;
cc:
load max(number) as max_number resident bb;
let v_max=peek('max_number',0,'cc');
drop table cc;
NoConcatenate
dd:
load *,
if(number=$(v_max),if(Activity=0, 1,0), if(Activity=0,peek(Previous('Trailing_Flag'))+1,0)) as Trailing_Flag;
load *//,recno() as number
resident bb order by Rep,Date desc;
drop table bb;
try this
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
aa:
LOAD * INLINE [
Rep, Date, Activity
ABC, 1/1/2016, 0
ABC, 1/2/2016, 0
ABC, 1/3/2016, 0
ABC, 1/4/2016, 1
ABC, 1/5/2016, 0
ABC, 1/6/2016, 5
ABC, 1/7/2016, 0
ABC, 1/8/2016, 0
ABC, 1/9/2016, 1
ABC, 1/10/2016, 3
ABC, 1/11/2016, 0
ABC, 1/12/2016, 2
];
NoConcatenate
bb:
load *,
if(number=1,if(Activity=0, 1,0), if(Activity=0,peek(Previous('Leading_Flag'))+1,0)
) as Leading_Flag;
load *,recno() as number
resident aa order by Rep,Date;
drop table aa;
cc:
load max(number) as max_number resident bb;
let v_max=peek('max_number',0,'cc');
drop table cc;
NoConcatenate
dd:
load *,
if(number=$(v_max),if(Activity=0, 1,0), if(Activity=0,peek(Previous('Trailing_Flag'))+1,0)) as Trailing_Flag;
load *//,recno() as number
resident bb order by Rep,Date desc;
drop table bb;
hope this help
Yes it works Florentina.
Thank you very much.
Hi,
I want to calculate 0's quarter year wise. I have tried order by REP,QuarterYear field but its not working.
Can you please help.
Thanks
Varsha