topic Table transformation in QlikView

Table transformation

Tue, 07 Apr 2015 12:01:46 GMT

Hi,

I figured out how to do simple transformations and rotations in QlikView but now I came across an excel "table" with both vertical and horizontal headers.

	Country	A	B
X	France	10	20
X	Ireland	30	40
Y	France	50	60
Y	Ireland	70	80

I need to transform the table in something useful that matches my data model: (never mind the sorting)

D	Country	X	Y
A	France	10	50
B	France	20	60
A	Ireland	30	70
B	Ireland	40	80

So the two last column headers A,B need to become values in a new column D. And the vertical headers need to become column headers.

I'm not even sure if QlikView can handle such a transformation.

Any input would be appreciated.

Alyson

Re: Table transformation

awhitfield — Tue, 07 Apr 2015 12:05:55 GMT

Hi Alyson,

you would do this use a Cross Table load, check out the Qlikview Reference Manual, there's an example similar to yours

Andy

Re: Table transformation

buzzy996 — Tue, 07 Apr 2015 12:17:08 GMT

i think pivot/cross table is the soltuon,

load ur data as it's

select chart type as pivot table and try to move ur objects rows as columns ans reversal.

Re: Table transformation

sunny_talwar — Tue, 07 Apr 2015 12:29:50 GMT

Try this:

Table:

CrossTable(D, Data, 2)

LOAD F1,

Country,

FROM

[https://community.qlik.com/thread/159077]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

Generic LOAD D,

Country,

F1,

Data

Resident Table;

DROP Table Table;

Also attaching the application (PFA)

Best,

Sunny

Re: Table transformation

chriscammers — Tue, 07 Apr 2015 12:35:46 GMT

I'm not sure why you would want to do this but here is a sample that uses crosstable to turn the "A" and "B" columns into dimension rows and then iterates over the original attribute containing "X" and "Y" to repivot by the original dimensions.

Produce the crosstable load

//The following statement will produce 1 row for each "data column"

//the attribute columns are defined by the third parameter of the

//crosstable statement in this case there are two attributes.

OriginalData:

CrossTable('NewAttribute','DataValue',2)

Load * Inline [

Attribute1,Country,A,B

X,France,10,20

X,Ireland,30,40

Y,France,50,60

Y,Ireland,70,80

];

Iterate over the field

Let vAttrCount = FieldValueCount('Attribute1');


For i=1 to vAttrCount
  Let vAttr = FieldValue('Attribute1',$(i));
  //Test to see if the table exists if no then create it else perform a join(Full Outer)
  If isnull(TableNumber('Final')) then
  Final:
  Load
  Country,
  NewAttribute,
  DataValue as [$(vAttr)]
  Resident OriginalData
  Where Attribute1 = '$(vAttr)';
  Else
  Join(Final)
  Load
  Country,
  NewAttribute,
  DataValue as [$(vAttr)]
  Resident OriginalData
  Where Attribute1 = '$(vAttr)';
  ENDIF


Next


//dropping original table to avoid invalid associations
Drop table OriginalData;

Re: Table transformation

Tue, 07 Apr 2015 13:11:06 GMT

Hi,

Just try by dragging and dropping the first column of your pivot table to the other orientation and the place where you required.

Priyantha.