Go through the attached file and see whether this is what you want
Crosstable-2headers.qvw 131.2 K
Check out the attached application. This has been achieved with the Enable Transformation option in QlikView.
Hope this helps you.
Amar Shedage Test.qvw 143.5 K
If you dont need the No. field - the very elegant solution is following:
1) by removing Col1 you convert it into 1x2 crosstable
2) by transpose you convert that to 2x1 crosstable
3) the rest is trivial - fill empty cells from above in Col1
4) Fix label on Col2
5) apply crosstable with 2 qualifying columns:
I.e. in script:
CrossTable([Company Name], Sales, 2)
LOAD * FROM [crosstable.xlsx] (ooxml, embedded labels, /* table is Sheet1, */ filters(
Remove(Col, Pos(Top, 1)),
Replace(1, top, StrCnd(null)),
Top(2, 'Sales Type')
Two-line-crosstable.qvw 155.2 K
Approach to solve this would be following
- Process grouped columns first using my previous solution
- Process ungrouped columns with this approach:
- delete all rows representing grouped data. As tthe power of filters is not sufficient to be completely generic, practical workaround would be to delete all rows that contain all Sales Type values; (i.e. Column2 = Individual or Column2 = Group). That will leave only the individual data rows.
- transpose back
- delete grouped headings - delete row where Column1 is empty
- JOIN data together
In script it looks like this:
JOIN LOAD * FROM [crosstable.xlsx] (ooxml, embedded labels, table is Sheet1, filters( Transpose(), Remove(Row, RowCnd(CellValue, 2, StrCnd(equal, 'Individual'))), // delete grouped rows - 1. value Remove(Row, RowCnd(CellValue, 2, StrCnd(equal, 'Group'))), // delete grouped rows - 2. value Transpose(), Remove(Row, RowCnd(CellValue, 1, StrCnd(null))) // remove header for grouped columns ));
And for those with more complex data layout, e..g:
where it is not enough to fill from neighbor cell and you need to combine two header lines (which is not possible in the load transformation) - solution is:
1) convert Nx2 crosstable into 3xN stored as CSV via transpose + calculate column + store
2) convert 3xN crosstable into Nx1 via load from CSV + remove original cols + transpose
You just need to be rather precise how labels are handled as QV is riddled with defects everywhere (especially when loading from QVD).