How to load a cross table with two headers?

[amars]

Hi all,

I have a cross table which has two headers rows. I now couldn't understand How to load that into the database & make it a straight table format. Please suggest.

Thanks in advance.

Amar .

[#2]

Hi Amar,

Check out the attached application. This has been achieved with the Enable Transformation option in QlikView.

Hope this helps you.

- Sridhar

[Anonymous]

Hi Sridhar,

Pls let me know how you did this? I have same secino in my project. Kindly help me in elobrating this.

Regards,

Nandha

[Anonymous]

Problem with this solution is that it hardcodes the number and names of crosstable columns - which is not helpful if you dont know the number of columns or their values ahead.

[Anonymous]

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)),

    Transpose(),

    Replace(1, top, StrCnd(null)),

    Top(2, 'Sales Type')

));

[Anonymous]

And for those with more complex data layout, e..g:

               ,2015,2015,...

Col1,Col2,Mar,Apr,...

x1,x2,val1,val2

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).

[Anonymous]

Please advise how to do it if I want to keep Individual and Group columns/fields. I also need No field.

[Anonymous]

Approach to solve this would be following

1. Process grouped columns first using my previous solution
2. Process ungrouped columns with this approach:
   • transpose
   • 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
3. 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
));