topic Re: How to load and do sum function in load script in QlikView

How to load and do sum function in load script

Thu, 07 Nov 2013 14:22:21 GMT

Hello,

I have data that follows the format of

Name, Date, Key, Data

A, 11/10/2012, 0, 100

A, 01/28/2010, 0, 20

A, 01/28/2013, 1, 30

B, 01/28/2013, 0, 33

I want to load the data so that, per each name if it has Key = 0, and is in last 12 months then sum of data should be a new field. So .. it should be like this .....

Name, Total Data

A, 130

B, 33

I can do it in set analysis, but I would like to do it load script, just create a new table, then have to just load that new table in tablebox. Anyway, I was hoping that you could direct me to right path, or show me how to accomplish this task.

Thank you for your time.

Re: How to load and do sum function in load script

Thu, 07 Nov 2013 14:27:48 GMT

Try something like this:

[Aggr.Table]

LOAD

Name AS [Aggr.Name],

Sum(Data) AS [Aggr.Total Data]

RESIDENT [OriginalTable]

WHERE (Key = 0) AND (Date >= AddMonths(Today(), -12))

GROUP BY Name;

Re: How to load and do sum function in load script

Colin-Albert — Thu, 07 Nov 2013 14:33:24 GMT

If your current data is in a table named "Data"

then you could use a script like this

Totals:

load

Name,

sum(Data) as Total

resident Data

where Key = 0

and Date >= addmonths(today(), -12)

group by Name ;

This will filter the data to select only records with Key = 0 and are in the last 12 months, and then sum the Data values by Name.

This will work, though normally it is better to keep the data at a granular level in QlikView and do the summing in your charts as this gives you the most flexibility on how to view and analyse the data.

Re: How to load and do sum function in load script

Thu, 07 Nov 2013 14:59:24 GMT

What would happen if there is no date or Key for that account, would I get nil value ?

Thanks btw ❤️

Re: How to load and do sum function in load script

Colin-Albert — Thu, 07 Nov 2013 19:25:18 GMT

If there are records with no date value then these would not be included in the Totals table. You could change the load script to replace any blank dates with a default value possibly. Similarly if the Key field is blank then those rows would also be excluded from the Totals table due to the where clause.