Aggregating in Load Script

[kerkying]

Hi all, asking for help in aggregating a table in Qlik Sense load script:

I have a source table [SOURCE TABLE]

Dimension	Value	Date
A	100	10/2/2019
B	20	10/2/2019
C	20	10/2/2019
D	20	10/2/2019
A	200	9/12/2019
B	20	9/12/2019
C	20	9/12/2019
D	20	9/12/2019

And I want to aggregate in load script to become something like this, [TARGET TABLE]

Date	Aggregated Value
10/2/2019	100/(20+20+20)
9/12/2019	200/(20+20+20)

Having trouble with the aggregation when i tried to use sum and group by to get the Aggregated Value column. I tried to use resident load to create 4 other tables eg.

[Target]:
Noconcatenate LOAD
Date,
SUM(Value) AS SUM_A
Resident [SOURCE TABLE]
WHERE Dimension='A'
GROUP BY [Date];

and then joining them together in another table but the records multiplied. Any advice is appreciated!

[Lisa_P]

Should work ok like this after your SOURCE TABLE:
Aggregate_A:
load
Date,
Sum(Value) as Sum_A
Resident [SOURCE TABLE]
Where Dimension = 'A'
Group by Date;

Aggregate_B:
load
Date,
Sum(Value) as Sum_B
Resident [SOURCE TABLE]
Where Dimension <> 'A'
Group by Date;

Then it is easy to calculate on the front end your final table using Date dimension and Sum_A/Sum_B for measure.

[kerkying]

Hi all,

In the end what was done was:-

Created another table with resident load to [SOURCE TABLE]:

[TARGET TABLE]:

LOAD 

Date,

SUM(IF(Dimension='A', Value))/SUM(IF(MATCH(Dimension, 'B', 'C', 'D'),Value)) AS [Aggregated Value]

Resident [SOURCE TABLE]

GROUP BY Date