How to make a sum pivot table in data load

[Lohmie]

Hi all,

I need to make a pivot table out of data with following format in the data load

Name	Year	value
A	2019	2
A	2019	3
A	2020	5
B	2020	4
B	2020	6
B	2021	8
C	2020	7
C	2020	6
..	..	..

I want the distinct values of name as rows and the year as columns, with the sum of values in the cells. So the result should look like this:

Name	2019	2020	2021	…
A	5	5	-
B	-	10	8
C	-	13	-
…

I know I can make a pivot table with the generic load and then sum up the values using SUM() and group by. But I haven't figured out how to automate that in case new data with a new year will be entered next year (Which will lead to another table being created by the generic load). Also getting the different tables by generic load and stitching them together again seems a bit of a hassle.

I saw some examples on the forum using FOR loops, but non which also need the sum of values and since I am new to qlik syntax I haven't figured out how to incorporate that.

Appreciate any help! Thanks!

[vinieme12]

I would structure the process as below

1) Lets say you have vertical data from 2010 to 2021

2) You do a one time transformation (generic load) and store it, lets call it Final.QVd

3) 2022 you receive new data in vertical format, 2022.csv

4) you transform only 2022.csv (generic load)  and store it  in say delta.qvd

5)

Final:

Load * Final.QVD

LEFT JOIN

Load * delta.qvd;

Store Final into Final.QVD;

-So you only process and add the delta every year

[Lohmie]

Hi, thanks for the reply! This is indeed a possibility, thanks! Ideally I would like to automate it completely, so if anyone has a solution to that I would appreciate it!