Convert Row values to Column in a table containing multiple dimension values

[#1]

Hi,

I got a table like below and would like to convert the row values to column.

Have explored Enable transformation transpose,left and right which is of not helping here. Tried generic load and it gets stuck with loading data script as may be volume is high.

Example:

Month     Country     State     Sales

Jan          US          S1          100

Jan          US          S2          200

Feb          US          S1          300

Feb          US          S2          400

Expected output:

Country     State     Jan     Feb

US               S1     100     300

US               S2      200     400

Actual table contains around 8 fields like country,state etc..Thanks in advance.

Regards,

Avin

[lironbaram]

hi

you can use cross table to bring the table to this state

Country     State     Month    Sales

US               S1     Jan     300

US               S2      Jan  400

and the use pivot table in the ui to get the display you are interested in

[MK_QSL]

Data:

Load * Inline

[

  Month,     Country,     State,     Sales

  Jan,          US,          S1,          100

  Jan,          US,          S2,          200

  Feb,          US,          S1,          300

  Feb,          US,          S2,          400

  Mar,          US,          S1,          500

  Mar,          US,          S2,          600

  Apr,          US,          S1,          700

  Apr,          US,          S2,          800

];

TempMonths:

Load Distinct Month as TempMonth Resident Data;

Let vMonth = FieldValueCount('TempMonth');

For i=0 to vMonth-1

Let vMonthName = Peek('TempMonth',$(i),'TempMonths');

  Final:

  Load

  Country,

  State,

  SUM(Sales) as '$(vMonthName)'

  Resident Data

  Where Month = '$(vMonthName)'

  Group By Country, State, Month;

Next

Drop Table TempMonths;

Drop Table Data;

Let vMonth = Null();

Let i = Null();

let vMonthName = Null();

[MK_QSL · Accepted solution]

Data:
Load * Inline
[
  Month,     Country,     State,     Sales
  Jan,          US,          S1,          100
  Jan,          US,          S2,          200
  Feb,          US,          S1,          300
  Feb,          US,          S2,          400
  Mar,          US,          S1,          500
  Mar,          US,          S2,          600
  Apr,          US,          S1,          700
  Apr,          US,          S2,          800
];


TempMonths:
Load Distinct Month as TempMonth Resident Data;


Let vMonth = FieldValueCount('TempMonth');


For i=0 to vMonth-1
Let vMonthName = Peek('TempMonth',$(i),'TempMonths');


  Final:
  Load
  Country,
  State,
  SUM(Sales) as '$(vMonthName)'
  Resident Data
  Where Month = '$(vMonthName)'
  Group By Country, State, Month;


Next


Drop Table TempMonths;


CombineTable:
Load Distinct Country Resident Data;


Drop Table Data;


FOR i = NoOfTables()-1 to 0 STEP -1  


  LET vTable = TableName($(i));


  IF WildMatch('$(vTable)', 'Final*') THEN
  LEFT JOIN ([CombineTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];  
  ENDIF


NEXT i


Let vMonth = Null();
Let i = Null();
Let vMonthName = Null();

[#5]

Thanks Manish

[Anonymous]

Hi Avin,

In my opinion you dont need to do any transformations in the back-end. you can simply load the data and then in the columns either use set analysis or if statements (you can use dynamic variables if you want to alter the column fields)

I have attached an example.

if you give more info regarding the end use of the fields i can alter the example.

Regards,

  Gareth

[#7]

Hi Gareth,

Yes I agree with your suggestion as well, another option is to use set analysis to create variables for each of this KPI like Jan Sales, Feb Sales etc..

The challenge is in my actual data, it is not months and this field which I want to convert to column can contain dynamic values.sometimes it may have 5 values or sometimes it may have totally different 3 values..

Hence getting it converted to columns in script and having separate KPI would help in this requirement.

Thanks,

Avin