Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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