topic Re: Display rows as columns and viceversa using Script in QlikView

Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 13:55:39 GMT

Hi all,

I am loading data from QVD's and the user wants the values to be displayed in columns rather than in rows which happens in traditional tables. For example,

Table:(Current)

Country Code Sales

America USA 2000

Australia Aus 2500

Germany Gmy 3000

Should be displayed as

Table:(Expected)

Country America Australia Germany

Code USA Aus Gmy

Sales 2000 2500 3000

and this has to be done only in the script not in visualization. I've tried crosstable, transpose function but nothing helped me completely.

Re: Display rows as columns and viceversa using Script

sunny_talwar — Fri, 11 Sep 2015 13:57:56 GMT

Use transpose functionality:

Step 1:

Step 2:

Re: Display rows as columns and viceversa using Script

sudeepkm — Fri, 11 Sep 2015 14:10:49 GMT

you can use a straight table in horizontal display mode as displayed below. It will be in the UI.

Re: Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 14:14:40 GMT

Hi Sunny,

Thank you for the quick response. I have tried your method before posting the question, it is not showing the fieldnames, just the values like below

@1 @2 @3

America Australia Germany

USA Aus Gmy

2000 2500 3000

also when a selection is made using listboxes like for example: Code= USA,

I am getting the result as

@1 @2 @3

USA Aus Gmy

When it should show like

America

USA

2000

Re: Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 14:15:51 GMT

Hi Sudeep,

Thank you for the suggestion, but I need it using only the script not in UI as I am working on a datamodel.

Re: Display rows as columns and viceversa using Script

sunny_talwar — Fri, 11 Sep 2015 14:21:54 GMT

Before:

After:

Script:

Table:

LOAD Country,

America,

Australia,

Germany

FROM

Community_180203.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Transpose()

));

Re: Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 14:36:18 GMT

Hi Sunny,

I have a question. Is it the same when loading from a qvd, because that is where I am loading the actual data and also the question posted is just for reference and not the exact one, so I have given only 3 fields and 3 values each. The original ones contain n number of values for each field. How to perform what you said in this case?

While loading using qvd, I am not getting the field names in enable transformation step.

Re: Display rows as columns and viceversa using Script

sunny_talwar — Fri, 11 Sep 2015 14:37:36 GMT

I am sure it should be available, but I can test it real quick

Re: Display rows as columns and viceversa using Script

sunny_talwar — Fri, 11 Sep 2015 14:41:17 GMT

It seems possible to Transpose a QVD.

Re: Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 14:46:13 GMT

Please, that might help me get the solution.

Re: Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 14:48:37 GMT

Then again, are you getting the field names displayed over there and in the resultant table?

Re: Display rows as columns and viceversa using Script

Anonymous — Fri, 11 Sep 2015 14:53:36 GMT

Here are the screenshots of the enable transformation step and the resultant table. As you can see no field names displayed.

Re: Display rows as columns and viceversa using Script

Anonymous — Mon, 14 Sep 2015 14:42:25 GMT

Hi All,

I've tried storing the qvd into the csv format based on the suggestions posted in the community and used the csv file to transpose the data and it seems to be working fine. Thank you all for the help.