How to load and do sum function in load script

[#1]

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.

[#2]

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;

[Colin-Albert · Accepted solution]

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.

[#4]

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

Thanks btw ❤️

[Colin-Albert]

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.