Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
Partner

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
Partner

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

florentina_doga
Partner
Partner

hope this help

udaya_kumar
Specialist
Specialist
Author

Yes it works Florentina.

Thank you very much.

Varsha
Contributor
Contributor

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