Solved: Re: Create Cumulative Count of Records in script ... - Page 2 - Qlik Community

mikelees80 · ‎2017-08-14

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

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

Example of Output

Date	Account	Count
01/07/2017	12345	0
02/07/2017	12345	0
03/07/2017	12345	0
04/07/2017	12345	0
05/07/2017	12345	0
06/07/2017	12345	0
07/07/2017	12345	0
08/07/2017	12345	0
09/07/2017	12345	0
10/07/2017	12345	0
11/07/2017	12345	0
12/07/2017	12345	0
13/07/2017	12345	0
14/07/2017	12345	0
15/07/2017	12345	1
16/07/2017	12345	2
17/07/2017	12345	2
18/07/2017	12345	2
19/07/2017	12345	3
20/07/2017	12345	3
21/07/2017	12345	3
22/07/2017	12345	3
23/07/2017	12345	3
24/07/2017	12345	3
25/07/2017	12345	4
26/07/2017	12345	4
27/07/2017	12345	4
28/07/2017	12345	4
29/07/2017	12345	5
30/07/2017	12345	5
31/07/2017	12345	5
01/08/2017	12345	5
02/08/2017	12345	6
03/08/2017	12345	6
04/08/2017	12345	6
05/08/2017	12345	7
06/08/2017	12345	7
07/08/2017	12345	7
08/08/2017	12345	7
09/08/2017	12345	7
10/08/2017	12345	8
11/08/2017	12345	8
12/08/2017	12345	9
13/08/2017	12345	9

Any ideas on how to produce this would be great

Kushal_Chawda · ‎2017-08-14

Another approach could be by using fastest way of calculating Min and max Date

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];

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 *,

alt(if(AutoNumber([Serial Number],Account) >0,AutoNumber([Serial Number],Account) ,Peek('Cumulative')),0) as Cumulative

Resident T1

Order by Account,InstallDate;

DROP Table T1;

Kushal_Chawda · ‎2017-08-14

or even more simpler

Data:

LOAD *, AutoNumber([Serial Number],Account) as Serial 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];

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 *,

alt(if(Serial>0,Serial,Peek('Cumulative')),0) as Cumulative

Resident T1

Order by Account,InstallDate;

DROP Table T1;

mikelees80 · ‎2017-08-14

Hi Krushal

This was very useful and I almost have this working, the issue I was when I add an additional Account the Cumulative starts at 1 instead 0

Any ideas

Kushal_Chawda · ‎2017-08-14

Hi Mike,

This could happen because let's say for Account B, you have serial number starts from installdate 1-08-2017 which is minimum date then your Cummulative will start with one as I have used autonumber. Do you want the it to start from 0?

mikelees80 · ‎2017-08-14

I need it to start at 0 for every account until it finds the first serial number ordered by date

Kushal_Chawda · ‎2017-08-14

try this

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];

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 *,

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

LOAD *,

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

Resident T1

Order by Account,InstallDate;

DROP Table T1;

mikelees80 · ‎2017-08-14

I'm still struggling

example account 1234 has a date 10/08/2015

when I select account 12345 years 2015 upto 15/07/2017 so Cumulative value of 1 however it needs to be 0

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, 5];

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 *,

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

LOAD *,

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

Resident T1

Order by Account,InstallDate;

DROP Table T1;

Anonymous · ‎2017-08-14

.. you asked for a script solution...

I think you need a frontend solution don't you?

mikelees80 · ‎2017-08-14

I then need to join this to another table to compare with sales, I tried to a front end solution using set analysis but couldn't get that to work.

I spoke to a various people at my organization on how to do this.

Its driving m crazy

Kushal_Chawda · ‎2017-08-14

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;

Create Cumulative Count of Records in script by date