Table transformation

[#1]

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
	Ireland	30	40
Y	France	50	60
	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

[awhitfield]

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

[buzzy996]

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.

[sunny_talwar]

Try this:

Table:

CrossTable(D, Data, 2)

LOAD F1,

    Country,

    A,

    B

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

[chriscammers]

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;

[#6]

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.