Rows to Columns

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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;

[marcus_sommer]

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

[Anonymous]

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"

[Anonymous]

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]

Ooops, I haven't read carefully enough.

- Marcus

[Anonymous]

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