Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
udaya_kumar
Specialist
Specialist

Find the no. of consecutive zeros and create a flag

Hi,

I have the data as shown here

  

Rep    Date  ActivityLeading_FlagTrailing_Flag
ABC1/1/2016013
ABC1/2/2016022
ABC1/3/2016031
ABC1/4/2016100
ABC1/5/2016011
ABC1/6/2016500
ABC1/7/2016012
ABC1/8/2016021
ABC1/9/2016100
ABC1/10/2016300
ABC1/11/2016011
ABC1/12/2016200

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.

1 Solution

Accepted Solutions
florentina_doga
Partner - Creator III
Partner - Creator III

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;

View solution in original post

4 Replies
florentina_doga
Partner - Creator III
Partner - Creator III

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;

florentina_doga
Partner - Creator III
Partner - Creator III

hope this help

udaya_kumar
Specialist
Specialist
Author

Yes it works Florentina.

Thank you very much.

Varsha
Contributor II
Contributor II

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