topic Transpose, Crosstable, Something Else ? in QlikView

Transpose, Crosstable, Something Else ?

richard_chilvers — Thu, 31 Jul 2014 08:13:26 GMT

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

Re: Transpose, Crosstable, Something Else ?

maleksafa — Thu, 31 Jul 2014 08:22:42 GMT

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.

Re: Transpose, Crosstable, Something Else ?

Anonymous — Thu, 31 Jul 2014 08:37:15 GMT

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

Re: Transpose, Crosstable, Something Else ?

richard_chilvers — Thu, 31 Jul 2014 09:21:37 GMT

Thanks for both replies.

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

Re: Transpose, Crosstable, Something Else ?

MarcoWedel — Thu, 31 Jul 2014 22:27:58 GMT

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