Pick and match Dynamically qlik sense script

[UserID2626]

Hi,

Is it possible to use pick and match dynamically while loading data.

Source:

load * inline

[

Sale_ID,Department, Quanity

A,2223X,23

B,2324A,123

C,1234S,234

D,2223qw,5

E,1234S,7

F,2223X,8

];

Final_table:

load Sale_ID,

pick(match(-1,Department='2223X',Department='2324A',Department='1234S',Department='2223qw'),1,2,3,4) as Department_id,

Department,

Quanity

resident Source;

Drop table Source;

if Department_id is added newly, is it possible new department_id called 5 dynamically.

Thanks..

[dplr-rn · Accepted solution]

Do you want to assign a particular number to a particular department or can that be dynamic?
e.g. you can use
AutoNumber(Department) as Dept_ID
and it will assign a number dynamically to each unique department.

If you want to control it e.g. Department='2223X' dept_id has to be 1 (and not 2)
it cannot be fully dynamic but you can use a applymap instead of pick match to make it more maintainable