Retrieve one value from multiple field based on matching 'class' field

[pepe2209]

Please look at the table below. I need to create the field "Type" in the script.

Class	0	1	2	3	4	Type
2	A	B	C	D	E	C
1	A	B	C	D	E	B
4	A	B	C	D	E	D
0	Z	X	Y	W	V	Z
1	Z	X	Y	W	V	X
1	Z	X	Y	W	V	X

The value for Type is retrieved from the field that matches with the corresponding value from Class.

Your help would be much appreciated.

Regards,

Peter

[mrossoit]

Mine is a very bad way to do it but at the moment is the only solution in my mind:

load *, if(class = 0, [0], if(class = 1, [1], [2])) as type;

LOAD * INLINE [

    class, 0, 1, 2

    2, A, B, C

    1, D, F, G

    0, C, B, N

];

[MK_QSL · Accepted solution]

Pick(Class+1, [0],[1],[2],[3],[4])

[Gabriel]

Hi,

Try this

Temp:
LOAD
Class,
[0],
[1],
[2],
[3],
[4]

FROM
[http://community.qlik.com/thread/157356]
(html, codepage is 1252, embedded labels, table is @1);

Final:

LOAD
*
, if(Class = 0, [0],
if(Class = 1, [1],
IF(Class = 2, [2],
IF(Class = 3, [3],
IF(Class = 4, [4]))))) AS Type

Resident Temp;

drop Table Temp;

[pepe2209]

This seems to work perfectly thanks!

[prieper]

The below should work also with non-numeric:

LOAD

  *,

  SUBFIELD(ConcatField, '|', Class +1) AS Type;

LOAD

  *,

  [0] & '|' & [1] & '|' & [2] & '|' & [3] & '|' & [4] AS ConcatField;

LOAD RECNO() AS id, * INLINE [

    Class, 0, 1, 2, 3, 4, DesiredType

    2, A, B, C, D, E, C

    1, A, B, C, D, E, B

    4, A, B, C, D, E, D

    0, Z, X, Y, W, V, Z

    1, Z, X, Y, W, V, X

    1, Z, X, Y, W, V, X];

HTH Peter