topic Re: How to create a table with aggregated values in load script? in QlikView

How to create a table with aggregated values in load script?

rajkapoor1 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to create a secondary table from my primary table to include aggregated values per dimension. For the example given below where I have a daily record of 'Item Total' I would like to create a table which sums it up by month. I would also like to add a column which gives the max and min date for each store. To put it in context, the difference between the max and min date gives me how long the store has been open for. Please note that I have only added records for only 5 days.

Is it possible to do this on the load script in QlikView? If so, what are the functions/load syntax that I can use to aggregate by store id and month and do the monthly sum?

Store ID	Item Total	COB Date	Snapshot
X	10	01/01/2018	Jan
X	10	02/01/2018	Jan
X	10	03/01/2018	Jan
X	10	04/01/2018	Jan
X	10	05/01/2018	Jan
X	5	01/02/2018	Feb
X	5	02/02/2018	Feb
X	5	03/02/2018	Feb
X	5	04/02/2018	Feb
X	5	05/02/2018	Feb
Y	7	01/01/2018	Jan
Y	7	02/01/2018	Jan
Y	7	03/01/2018	Jan
Y	7	04/01/2018	Jan
Y	7	05/01/2018	Jan
Y	6	01/02/2018	Feb
Y	6	02/02/2018	Feb
Y	6	03/02/2018	Feb
Y	6	04/02/2018	Feb

Aggregated table I want to achieve:

Store ID	Item Total	Snapshot	Min Date	Max Date
X	50	Jan	01/01/2018	05/02/2018
X	25	Feb	01/01/2018	05/02/2018
Y	35	Jan	01/01/2018	05/02/2018
Y	24	Feb	01/01/2018	05/02/2018

Many thanks,

Raj

Re: How to create a table with aggregated values in load script?

sunny_talwar — Thu, 12 Apr 2018 15:41:43 GMT

Try this

Table:

LOAD * INLINE [

Store ID, Item Total, COB Date, Snapshot

X, 10, 01/01/2018, Jan

X, 10, 02/01/2018, Jan

X, 10, 03/01/2018, Jan

X, 10, 04/01/2018, Jan

X, 10, 05/01/2018, Jan

X, 5, 01/02/2018, Feb

X, 5, 02/02/2018, Feb

X, 5, 03/02/2018, Feb

X, 5, 04/02/2018, Feb

X, 5, 05/02/2018, Feb

Y, 7, 01/01/2018, Jan

Y, 7, 02/01/2018, Jan

Y, 7, 03/01/2018, Jan

Y, 7, 04/01/2018, Jan

Y, 7, 05/01/2018, Jan

Y, 6, 01/02/2018, Feb

Y, 6, 02/02/2018, Feb

Y, 6, 03/02/2018, Feb

Y, 6, 04/02/2018, Feb

];

AggrTable:

LOAD [Store ID],

Sum([Item Total]) as [Item Total],

Snapshot,

Date(Min([COB Date])) as MinDate,

Date(Max([COB Date])) as MaxDate

Resident Table

Group By [Store ID], Snapshot;

DROP Table Table;

Re: How to create a table with aggregated values in load script?

rajkapoor1 — Thu, 12 Apr 2018 16:00:16 GMT

Hi - can I still follow the same process but keep the two tables? i.e not do the drop table table part

Re: How to create a table with aggregated values in load script?

sunny_talwar — Thu, 12 Apr 2018 16:03:01 GMT

Sure, don't drop your main table

Re: How to create a table with aggregated values in load script?

rajkapoor1 — Thu, 12 Apr 2018 16:07:44 GMT

Thanks Sunny - I'll try that and get back to you with any feedback