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