Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mikelees80
New Contributor

Create Cumulative Count of Records in script by date

So I've been asked to provide cumulative count of our products we have installed by date. I need to to make sure we include date even if we haven't installed anything on that that. I already have a calendar QVD I can leverage.

This needs to be a script because I need to then link this new table to a different data set

Example of installed list

   

AccountInstallDateSerial Number
1234515/07/201710
1234516/07/201711
1234519/07/201712
1234525//07/201713
1234529/07/201714
1234502/08/201715
1234505/08/201716
1234510/08/201717
1234512/08/201718

Example of Output

   

DateAccountCount
01/07/2017123450
02/07/2017123450
03/07/2017123450
04/07/2017123450
05/07/2017123450
06/07/2017123450
07/07/2017123450
08/07/2017123450
09/07/2017123450
10/07/2017123450
11/07/2017123450
12/07/2017123450
13/07/2017123450
14/07/2017123450
15/07/2017123451
16/07/2017123452
17/07/2017123452
18/07/2017123452
19/07/2017123453
20/07/2017123453
21/07/2017123453
22/07/2017123453
23/07/2017123453
24/07/2017123453
25/07/2017123454
26/07/2017123454
27/07/2017123454
28/07/2017123454
29/07/2017123455
30/07/2017123455
31/07/2017123455
01/08/2017123455
02/08/2017123456
03/08/2017123456
04/08/2017123456
05/08/2017123457
06/08/2017123457
07/08/2017123457
08/08/2017123457
09/08/2017123457
10/08/2017123458
11/08/2017123458
12/08/2017123459
13/08/2017123459

Any ideas on how to produce this would be great

1 Solution

Accepted Solutions

Re: Create Cumulative Count of Records in script by date

try this, seems to be working

Data:

LOAD * Inline [

Account, InstallDate, Serial Number

12345, 15/07/2017, 10

12345, 16/07/2017, 11

12345, 19/07/2017, 12

12345, 25/07/2017, 13

12345, 29/07/2017, 14

12345, 02/08/2017, 15

12345, 05/08/2017, 16

12345, 10/08/2017, 17

12345, 12/08/2017, 18

1234, 10/8/2015, 18

];

T1:

LOAD date(MinDate+IterNo()-1) as InstallDate

While MinDate+IterNo()-1<=MaxDate;

LOAD monthstart(min(Date)) as MinDate,

          max(Date) as MaxDate;

LOAD FieldValue('InstallDate',RecNo()) as Date

AutoGenerate FieldValueCount('InstallDate');

Left Join(T1)

LOAD Distinct Account

Resident Data;

Left Join(T1)

LOAD *

Resident Data;

DROP Table Data;

Final:

LOAD *,

         if(Serial=0,if(Account=peek(Account),Peek('Cumulative'),Serial),Serial) as Cumulative;

LOAD *,

         autonumber(alt([Serial Number],'NA'),Account)-1 as Serial

Resident T1

Order by Account,InstallDate;

DROP Table T1;

20 Replies
roharoha
Valued Contributor III

Re: Create Cumulative Count of Records in script by date

1) count your installed list, I guess grouped InstallDate like that:

TEMP:

load

count(InstallDate) as Count

Installdate

resident YOURTABLE

group by InstallDate;

2) do a left join : YOURCALENDAR.qvd left join TEMP via Keyfield InstallDate

3) sum up everything

FINAL:

InstallDate,

Rangesum(Peek('CumulativeCount'), Count) as CumulativeCount

resident YOURJOINEDTABLE;

devarasu07
Honored Contributor II

Re: Create Cumulative Count of Records in script by date

Hi,

Can you try like below?


Data:

LOAD * INLINE [

Account, InstallDate, Serial_Number

    12345, 15/07/2017, 10

    12345, 16/07/2017, 11

    12345, 19/07/2017, 12

    12345, 25//07/2017, 13

    12345, 29/07/2017, 14

    12345, 02/08/2017, 15

    12345, 05/08/2017, 16

    12345, 10/08/2017, 17

    12345, 12/08/2017, 18

];

Fact:

LOAD

    Account,

    InstallDate,

    Count(Serial_Number) + If(IsNull(Peek('Cumulative')), 0, Peek('Cumulative')) AS Cumulative

Resident Data

GROUP BY Account, InstallDate;

DROP TABLE Data;

Thanks,Deva

roharoha
Valued Contributor III

Re: Create Cumulative Count of Records in script by date

if different accounts should be separated?!?

then you should add a further condition like

if (account <> previous(acount) ... ) ...

mikelees80
New Contributor

Re: Create Cumulative Count of Records in script by date

Yes I need to split the count by account

roharoha
Valued Contributor III

Re: Create Cumulative Count of Records in script by date

do you get it or do you need further help?

mikelees80
New Contributor

Re: Create Cumulative Count of Records in script by date

I'm trying it now Robin

Thanks for the advice and i'll let you know how I get on

devarasu07
Honored Contributor II

Re: Create Cumulative Count of Records in script by date

Hi,

Try like this,

Data:

LOAD InstallDate as Date,* INLINE [

Account, InstallDate, Serial_Number

    12345, 15/07/2017, 10

    12345, 16/07/2017, 11

    12345, 19/07/2017, 12

    12345, 25//07/2017, 13

    12345, 29/07/2017, 14

    12345, 02/08/2017, 15

    12345, 05/08/2017, 16

    12345, 10/08/2017, 17

    12345, 12/08/2017, 18

    12340, 10/08/2017, 37

    12340, 12/08/2017, 68

];

Fact:

LOAD

    Account,

    Date,

     If(Account = Previous(Account),Count(Serial_Number) + If(IsNull(Peek('Cumulative')), 0, Peek('Cumulative'))) AS Cumulative

Resident Data

GROUP BY Account, Date;

DROP TABLE Data;

mikelees80
New Contributor

Re: Create Cumulative Count of Records in script by date

Thanks for this solution however I need a Cumulative count for every day even if there aren't any install dates

ie 11/08/17

How would your code cope this this

antoniotiman
Honored Contributor III

Re: Create Cumulative Count of Records in script by date

Hi Mike,

TableA:
LOAD * Inline [
Account, InstallDate, Serial Number
12345, 15/07/2017, 10
12345, 16/07/2017, 11
12345, 19/07/2017, 12
12345, 25/07/2017, 13
12345, 29/07/2017, 14
12345, 02/08/2017, 15
12345, 05/08/2017, 16
12345, 10/08/2017, 17
12345, 12/08/2017, 18]
;
LOAD Min(InstallDate) as MinDate,Max(InstallDate) as MaxDate
Resident TableA;
LET vMinDate = Peek('MinDate',RecNo());
LET vMaxDate = Peek('MaxDate',RecNo());
TableB:
LOAD DISTINCT Account,Date(MonthStart($(vMinDate))+IterNo()-1) as InstallDate
Resident TableA
While Date(MonthStart($(vMinDate))+IterNo()-1) <= $(vMaxDate);
Left Join (TableB) LOAD * Resident TableA;
LOAD *,If(Account=Peek(Account) and Len(Trim([Serial Number])) > 0,
RangeSum(Peek(Cumulative),1),RangeSum(Peek(Cumulative),0)) as Cumulative
Resident TableB Order By Account,InstallDate;
Drop Table
TableA,TableB;

Regards,

Antonio

Community Browser