topic Rows to Columns in QlikView

Rows to Columns

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Sunny,

I am able to get the values in the front end already. But I need to achieve something for which, building this conversion in the script is important.

I have the following columns in a table:

Month Price

------- --------

Jan 2016 50

Feb 2016 60

Mar 2016 34

Jan 2016 65

Mar 2016 45

Jan 2015 87

Feb 2016 66

...

-------------------------

I need the month rows to become columns as below:

Jan 2016 Feb 2016 Mar 2016

------------ -------------- --------------

50 60 34

65 66 45

The data is being loaded from existing table. So it is a resident load.

I just need to achieve this.

Re: Rows to Columns

sunny_talwar — Wed, 20 Apr 2016 17:55:06 GMT

Try this out:

Table:

LOAD *,

AutoNumber(RowNo(), Month) as Key

Inline [

Month, Price

Jan 2016, 50

Feb 2016, 60

Mar 2016, 34

Jan 2016, 65

Mar 2016, 45

Jan 2016, 87

Feb 2016, 66

];

FinalTable:

LOAD 0 as Dummy

AutoGenerate 0;

FOR i = 1 to FieldValueCount('Month')

LET vField = FieldValue('Month', $(i));

LET vField1 = '[' & FieldValue('Month', $(i)) & ']';

Join(FinalTable)

LOAD Key,

Price as $(vField1)

Resident Table

Where Month = '$(vField)';

NEXT

DROP Table Table;

DROP Field Dummy;

Re: Rows to Columns

marcus_sommer — Wed, 20 Apr 2016 18:06:58 GMT

Are you sure that you need a transforming of the table within the script? Then within the gui you could simply use a pivot-chart to get the same result.

- Marcus

Re: Rows to Columns

sunny_talwar — Wed, 20 Apr 2016 18:08:09 GMT

He doesn't seem to want to do it on the front end -> Rows to columns

Re: Rows to Columns

Anonymous — Wed, 20 Apr 2016 18:09:46 GMT

Field names must be unique within table

Table:

LOAD *,

AutoNumber(RowNo(), Month) as Key,

Month,

Price

resident Original;

I am getting this error while load Sunny.

The data is through resident load from the table "Original"

Re: Rows to Columns

Anonymous — Wed, 20 Apr 2016 18:10:39 GMT

Hi Marcus,

I need to perform few more operations on the table. So getting them in Front End is not helpful. I already got that on my front end long back ago, but this time, it should be in the script.

Re: Rows to Columns

sunny_talwar — Wed, 20 Apr 2016 18:11:26 GMT

May be you need to do this:

LOAD *,

AutoNumber(RowNo(), Month) as Key

Resident Original;

Re: Rows to Columns

marcus_sommer — Wed, 20 Apr 2016 18:12:43 GMT

Ooops, I haven't read carefully enough.

- Marcus

Re: Rows to Columns

Anonymous — Wed, 20 Apr 2016 18:13:28 GMT

LOAD *, I think that caused the error. I am only pulling two columns and I don't need *. Reloading again without *

Re: Rows to Columns

sunny_talwar — Wed, 20 Apr 2016 18:14:41 GMT

Sounds like a plan

Re: Rows to Columns

Anonymous — Wed, 20 Apr 2016 18:28:03 GMT

Sunny, thank you so much. This is working. Glad that I finally found it

Re: Rows to Columns

sunny_talwar — Wed, 20 Apr 2016 18:30:41 GMT

Great, I am glad you finally found it.

I would suggest you to close your other thread as well. You can mark it as assumed answer, but do mark any helpful answers if you found any on the earlier thread.

Best,

Sunny

Re: Rows to Columns

gauravgg — Mon, 23 Jul 2018 11:31:57 GMT

Hi sunny ,

in this case you have you have two fields , Month and Price .

I have same data but with many fields .

In that case how the code will work , in am facing issue .

can you help me out in this .

Re: Rows to Columns

sunny_talwar — Mon, 23 Jul 2018 11:36:02 GMT

Would you be able to share a sample to show what you have?