topic Convert table with diagonal values to vertical table in QlikView

Convert table with diagonal values to vertical table

qlikmark1990 — Mon, 21 Jan 2019 14:04:13 GMT

I currently have the following table in my QlikView application :

CumRijNummer	29700	-108146	-259585	-443525	-460585	-501017	-382687	-400837	-346605	-281085	719451	529451
1	29700	-	-	-	-	-	-	-	-	-	-	-
2	-	-108146	-	-	-	-	-	-	-	-	-	-
3	-	-	-259585	-	-	-	-	-	-	-	-	-
4	-	-	-	-443525	-	-	-	-	-	-	-	-
5	-	-	-	-	-460585	-	-	-	-	-	-	-
6	-	-	-	-	-	-501017	-	-	-	-	-	-
7	-	-	-	-	-	-	-382687	-	-	-	-	-
8	-	-	-	-	-	-	-	-400837	-	-	-	-
9	-	-	-	-	-	-	-	-	-346605	-	-	-
10	-	-	-	-	-	-	-	-	-	-281085	-	-
11	-	-	-	-	-	-	-	-	-	-	719451	-
12	-	-	-	-	-	-	-	-	-	-	-	529451
13	-	-	-	-	-	-	-	-	-	-	-	529451
14	-	-	-	-	-	-	-	-	-	-	-	529451
15	-	-	-	-	-	-	-	-	-	-	-	529451
16	-	-	-	-	-	-	-	-	-	-	-	529451

I need to convert the table to the following :

CumRijNummer	SaldoLiquideMiddelen
1	29700
2	-108146
3	-259585
4	-443525
5	-460585
6	-501017
7	-382687
8	-400837
9	-346605
10	-281085
11	719451
12	529451
13	529451
14	529451
15	529451
16	529451

What is an easy way to convert this table? I only could find information about convert a table from horizontal to vertical and visa versa.

Re: Convert table with diagonal values to vertical table

MindaugasBacius — Mon, 21 Jan 2019 14:19:04 GMT

tmp:
Directory;
CrossTable(SaldoLiquideMiddelen, Data)
LOAD [CumRijNummer ], 
     [29700], 
     [-108146], 
     [-259585], 
     [-443525], 
     [-460585], 
     [-501017], 
     [-382687], 
     [-400837], 
     [-346605], 
     [-281085], 
     [719451], 
     [529451]
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);


NoConcatenate
tmp2:
LOAD [CumRijNummer ]
	,SaldoLiquideMiddelen
Resident tmp
Where Data = SaldoLiquideMiddelen
;

DROP Table tmp;

Try the code above.

If it works then I suggest reading about Crosstable 🙂

The results:

Re: Convert table with diagonal values to vertical table

MindaugasBacius — Mon, 21 Jan 2019 14:20:11 GMT

Also, the qvw.