topic Re: consecutive count in script. in QlikView

consecutive count in script.

iahmadmca1 — Wed, 10 Jan 2018 15:41:21 GMT

Hi Team,

Just I want to small help from you.I have a sample data based on sample data, I want to consecutive count.Provided the below screenshots for your reference.

based on the Account number and start_date the consecutive count should be the (4).becous start_date month is not coming continuously for the Account number.

The below logic which is using to bring the consecutive count

LOAD Account_Number,

Billing_Year_Month,

End_date,

start_date

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Consecutive:

LOAD

Account_Number,

if(rowno()=1 or start_date = previous(start_date),1,

if(Billing_Year_Month-previous(Billing_Year_Month)=1, peek('Counter')+1,peek(_flag_Consec))) as _flag_Consec

Resident 1 Order By Account_Number,Billing_Year_Month,start_date asc;

Also attached the sample data which is using

Please correct the script and share your idea.

Thanks,

Irshad Ahmad

Re: consecutive count in script.

sunny_talwar — Wed, 10 Jan 2018 15:46:04 GMT

Not sure I understand why the count should be 4? Can you elaborate a little?

Re: consecutive count in script.

iahmadmca1 — Wed, 10 Jan 2018 16:26:14 GMT

Thanks Sunny for the quick response.

The count should be four because the start_date month is not coming continuously.

The Account_Number is same for all five but the start_date month is not coming continuously.i have highlighted the below in screenshots.

Due to that, the consecutive count should be 4.

Thanks,

Irshad Ahmad

Re: consecutive count in script.

sunny_talwar — Wed, 10 Jan 2018 16:40:19 GMT

How does this look like

Table:

LOAD Account_Number,

Billing_Year_Month,

End_date,

start_date

FROM

[..\..\Downloads\Billing.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FinalTable:

LOAD *,

If(Account_Number = Previous(Account_Number) and (MonthStart(start_date) = MonthStart(Previous(start_date), 1) or MonthStart(start_date) = MonthStart(Previous(start_date), 0)), RangeSum(Peek('Consecutive'), 1), 1) as Consecutive

Resident Table

Order By Account_Number, start_date;

Left Join (FinalTable)

LOAD Account_Number,

Max(Consecutive) as MaxConsecutive

Resident FinalTable

Group By Account_Number;

DROP Table Table;

Re: consecutive count in script.

iahmadmca1 — Wed, 10 Jan 2018 17:07:52 GMT

Sunny,

This is sample data which I have shared, I have N number of Account_Number and all Account_Number having the different consecutive count from there itself I need a count the consecutive count.

Please find the below-expected output:

Re: consecutive count in script.

sunny_talwar — Wed, 10 Jan 2018 17:09:38 GMT

That's what the above will do for you

Re: consecutive count in script.

iahmadmca1 — Wed, 10 Jan 2018 17:48:02 GMT

Thanks, Sunny it's working perfectly.