Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmark1990
Contributor III
Contributor III

Convert table with diagonal values to vertical table

I currently have the following table in my QlikView application :

CumRijNummer 29700-108146-259585-443525-460585-501017-382687-400837-346605-281085719451529451
129700-----------
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 
129700
2-108146
3-259585
4-443525
5-460585
6-501017
7-382687
8-400837
9-346605
10-281085
11719451
12529451
13529451
14529451
15529451
16529451

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.

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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:

Screenshot_1.jpg

View solution in original post

2 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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:

Screenshot_1.jpg

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Also, the qvw.