Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rajkapoor1
Contributor III
Contributor III

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

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 IDItem TotalCOB DateSnapshot
X1001/01/2018Jan
X1002/01/2018Jan
X1003/01/2018Jan
X1004/01/2018Jan
X1005/01/2018Jan
X501/02/2018Feb
X502/02/2018Feb
X503/02/2018Feb
X504/02/2018Feb
X505/02/2018Feb
Y701/01/2018Jan
Y702/01/2018Jan
Y703/01/2018Jan
Y704/01/2018Jan
Y705/01/2018Jan
Y601/02/2018Feb
Y602/02/2018Feb
Y603/02/2018Feb
Y604/02/2018

Feb

Aggregated table I want to achieve:

   

Store IDItem TotalSnapshotMin DateMax Date
X50Jan01/01/201805/02/2018
X25Feb01/01/201805/02/2018
Y35Jan01/01/201805/02/2018
Y24Feb01/01/201805/02/2018

Many thanks,

Raj

4 Replies
sunny_talwar

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;

rajkapoor1
Contributor III
Contributor III
Author

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

sunny_talwar

Sure, don't drop your main table

rajkapoor1
Contributor III
Contributor III
Author

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