Transpose, Crosstable, Something Else ?

[richard_chilvers]

Hi

I have a spreadsheet created by another system which looks like this:

Code	Grade	A	B	C	D	E
M1412	Cruise	1	2	3	4	5
	Flight	2	3	1
M1413	Cruise	2	4	6	8
	Flight	1

I would like to load the data as follows:

Code	Grade	Cruise	Flight
M1412	A	1	2
M1412	B	2	3
M1412	C	3	1
M1412	D	4
M1412	E	5
M1413	A	2	1
M1413	B	4
M1413	C	6
M1413	D	8
M1413	E

Is this possible using Excel import parameters, or QV LOAD parameters? Or is this step too far?

Thanks

Richard

[maleksafa]

so first step you want to do is to fill the missing code, you can do that from the 'Enable Transformation Step' wizard, go to fill tab and do the setup there with the condition if the cell is empty to take the value from the above cell, this wizard will fill the missing cells.

then you want to use the cross table also from the import wizard to transform the above table (filled with all the cells) to your format.

[Anonymous]

This can be done with excel transformations and crosstable and with little bit joining. See the attachement.

[richard_chilvers]

Thanks for both replies.

I will work with the attachment and modify it to my 'live' data. It looks promising

[MarcoWedel]

Hi Richard,

another solution could be:

table1:
CrossTable (GradeNew, Value, 2)
LOAD * FROM [http://community.qlik.com/thread/127821]
(html, codepage is 1252, embedded labels, table is @1, filters(
Replace(1, top, StrCnd(null))));

RENAME Field Grade to GradeOld, GradeNew to Grade;

Left Join (table1)
LOAD Distinct
  Code, Grade, AutoNumberHash128(Code, Grade) as ID
Resident table1;

table2:
Generic LOAD
  ID, GradeOld, Value
Resident table1;

DROP Field GradeOld;

This script should work regardless of the number of grade columns and values and without the need for any hardcoded field values.

hope this helps as well

regards

Marco