Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rajkapoor1
New 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

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

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
New Contributor III

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

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?

Sure, don't drop your main table

rajkapoor1
New Contributor III

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

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