Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
drj12345
Contributor III
Contributor III

Rows to Columns

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.

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

13 Replies
sunny_talwar

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;


Capture.PNG

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

sunny_talwar

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

drj12345
Contributor III
Contributor III

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"

drj12345
Contributor III
Contributor III

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.

sunny_talwar

May be you need to do this:

LOAD *,

  AutoNumber(RowNo(), Month) as Key

Resident Original;

marcus_sommer
MVP & Luminary
MVP & Luminary

Ooops, I haven't read carefully enough.

- Marcus

drj12345
Contributor III
Contributor III

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

sunny_talwar

Sounds like a plan