topic Re: Retrieve one value from multiple field based on matching 'class' field in QlikView

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

pepe2209 — Tue, 24 Mar 2015 14:33:34 GMT

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

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

mrossoit — Tue, 24 Mar 2015 14:44:42 GMT

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

];

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

MK_QSL — Tue, 24 Mar 2015 14:45:02 GMT

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

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

Gabriel — Tue, 24 Mar 2015 14:50:58 GMT

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;

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

pepe2209 — Tue, 24 Mar 2015 15:20:15 GMT

This seems to work perfectly thanks!

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

prieper — Tue, 24 Mar 2015 15:22:40 GMT

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