topic How to convert cross table into straight table in QlikView

How to convert cross table into straight table

shruthibk — Mon, 02 Nov 2015 07:19:19 GMT

Hi,

I have raw data available in a cross table format, now I want to load this cross table as straight table in QlikView.

Please guide me to perform this.

Thanks,

shruthi

Re: How to convert cross table into straight table

oknotsen — Mon, 02 Nov 2015 10:08:09 GMT

Assuming you are calling the top row "Year" and the values "Value", add this line directly ABOVE your load statement:

CrossTable(Year, Value, 0)

Re: How to convert cross table into straight table

prma7799 — Mon, 02 Nov 2015 10:11:34 GMT

CrossTable(Year, Data, 0)

LOAD [2012],

[2013],

[2014]

FROM..........

Re: How to convert cross table into straight table

prma7799 — Mon, 02 Nov 2015 10:12:30 GMT

PFA...

Re: How to convert cross table into straight table

shruthibk — Mon, 02 Nov 2015 10:14:38 GMT

Hi,thanks for your response but it is not giving the expected result.It is loading the data as it is.

expected output is

Year,Value

2012,10

2013,20

2014,30

2012,20

2013,30

2014,40

2012,40

2013,50

2014,60

thanks,

shruthi

Re: How to convert cross table into straight table

shruthibk — Mon, 02 Nov 2015 10:18:12 GMT

Re: How to convert cross table into straight table

prma7799 — Mon, 02 Nov 2015 10:23:06 GMT

Can you please send some sample qvw file.

Re: How to convert cross table into straight table

oknotsen — Mon, 02 Nov 2015 10:24:12 GMT

Interesting. You seemed to have found a bug.

I created a work-around. This script (tested) does the trick:

YourTable:
CrossTable(Year, Data, 1)
LOAD 1 as BogusField,
[2012],
[2013],
[2014]
FROM
CrossTable.xlsx
(ooxml, embedded labels, table is Sheet1);

drop field BogusField;

Re: How to convert cross table into straight table

shruthibk — Mon, 02 Nov 2015 10:26:59 GMT

thanks,this code is working

Re: How to convert cross table into straight table

prma7799 — Mon, 02 Nov 2015 10:27:44 GMT

Hi Shruti,

Just try okg Solution

Thanks

Re: How to convert cross table into straight table

oknotsen — Mon, 02 Nov 2015 10:29:20 GMT

Great .

If that answers your question, please flag a Correct and optionally a Helpful answer.

If not, please let us know what information related to this topic you are still looking for.

Re: How to convert cross table into straight table

oknotsen — Mon, 02 Nov 2015 10:31:45 GMT

Check your data model and the fields available in your solution and you will see they are not correct.

Apparently this is a bug in QlikView which happens when there are no qualifying fields in a CrossTable load.

Re: How to convert cross table into straight table

prma7799 — Mon, 02 Nov 2015 10:35:19 GMT

Yes your right but I already told her that your solution is right.

I am giving solution base her requirement.

please check my reply.

Thanks

Re: How to convert cross table into straight table

shruthibk — Mon, 02 Nov 2015 10:39:37 GMT

thank you all for your response

thanks @Onno van Knotsenburg