topic Re: Convert Row values to Column in a table containing multiple dimension values in QlikView

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

Tue, 14 Jul 2015 06:06:33 GMT

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

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

lironbaram — Tue, 14 Jul 2015 06:39:01 GMT

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

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

MK_QSL — Tue, 14 Jul 2015 06:46:00 GMT

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;

Drop Table TempMonths;

Drop Table Data;

Let vMonth = Null();

Let i = Null();

let vMonthName = Null();

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

MK_QSL — Tue, 14 Jul 2015 06:50:57 GMT

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();

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

Tue, 14 Jul 2015 06:59:07 GMT

Thanks Manish

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

Anonymous — Tue, 14 Jul 2015 07:00:24 GMT

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

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

Tue, 14 Jul 2015 07:42:24 GMT

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