topic Re: How to load a cross table with two headers? in QlikView

How to load a cross table with two headers?

amars — Thu, 28 Jul 2011 07:35:32 GMT

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 .

Re: How to load a cross table with two headers?

Thu, 28 Jul 2011 13:36:23 GMT

Hi, I think so!

How to load a cross table with two headers?

Thu, 28 Jul 2011 15:03:10 GMT

Wow I've wasted so much time using Excel because I thought you couldn't do this.. but I still don't know how to do it from your example. How did you do that!?!?!?!?

How to load a cross table with two headers?

Thu, 28 Jul 2011 15:09:40 GMT

Well I read the little "Designer II" book and figured it out.. you drag the dimension above the EXPRESSION.

This is actually going to save me an hour today so thanks a lot.

How to load a cross table with two headers?

Thu, 28 Jul 2011 18:58:33 GMT

Hi, could you share the excels files to learn it better.

Thanks by advance!

How to load a cross table with two headers?

amars — Fri, 29 Jul 2011 03:01:08 GMT

Hi Kostin,

I know this thing. I wanted to use the excel file as my datasource, for which I will need to use the crosstable function.

I don't want to show a pivot just like the excel but need to import an excel which looks like this, in qlikview dashboard.

Please help. Thanks in advance.

Amar

Re: How to load a cross table with two headers?

Mon, 01 Aug 2011 11:16:31 GMT

Hi, you can download Excel from first message by amars.

Re: How to load a cross table with two headers?

Mon, 01 Aug 2011 11:21:55 GMT

Hi, I cut the original file into three parts and then joined the parameter "atr". The first part of this is the first title, the second - the second heading, the third - the main body of data.

Re: How to load a cross table with two headers?

Mon, 01 Aug 2011 11:32:08 GMT

Hi, amars,I do not understand what you need. Can this option is suitable?

Re: How to load a cross table with two headers?

qliksus — Mon, 01 Aug 2011 13:40:43 GMT

Hi ,

Go through the attached file and see whether this is what you want

Re: How to load a cross table with two headers?

Mon, 01 Aug 2011 15:01:58 GMT

Hi Amar,

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

Hope this helps you.

- Sridhar

Re: How to load a cross table with two headers?

Anonymous — Tue, 11 Dec 2012 22:16:13 GMT

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

Re: How to load a cross table with two headers?

Anonymous — Thu, 30 Apr 2015 12:04:04 GMT

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.

Re: How to load a cross table with two headers?

Anonymous — Mon, 04 May 2015 23:23:32 GMT

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

));

Re: How to load a cross table with two headers?

Anonymous — Mon, 04 May 2015 23:35:52 GMT

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

Re: How to load a cross table with two headers?

Anonymous — Fri, 23 Dec 2016 17:18:35 GMT

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

Re: How to load a cross table with two headers?

Anonymous — Wed, 08 Feb 2017 23:56:56 GMT

Approach to solve this would be following

Process grouped columns first using my previous solution
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
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
));