Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Pick(Class+1, [0],[1],[2],[3],[4])
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
];
Pick(Class+1, [0],[1],[2],[3],[4])
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;
This seems to work perfectly thanks!
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