consecutive count in script.

[iahmadmca1]

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

1:

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

[sunny_talwar]

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

[iahmadmca1]

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

[sunny_talwar · Accepted solution]

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;

[iahmadmca1]

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:

[sunny_talwar]

That's what the above will do for you

[iahmadmca1]

Thanks, Sunny it's working perfectly.