Adding a new column with value based on value of other column

[guya]

Hello,

I block on a small issue I'm sure you could help me on.

I want to add another column during loading named "MemberType" and the values are based on value of other column.

e.g.

ID	Name	MemberType (New Column)
1	AAA	Direct
2	BBB	Direct
3	CCC	Indirect
4	DDD	Indirect
5	EEE	Indirect
6	FFF	ToSign

if ID = 1, 2 then MemberType is 'Direct'

if ID= 3,4,5 then MemberType is 'indirect'

If ID =6 then MemberType is 'ToSign'

Thx in advance for your support,

Guy

[jatishqv · Accepted solution]

Hi,

The best way to this is use a mapping table and ApplyMap. It fundamentally works like a vlookup in excel.

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

Map_MemberType:
Mapping
LOAD * INLINE [
    ID, MemberType
    1, Direct
    2, Direct
    3, Indirect
    4, Indirect
    5, Indirect
    6, ToSign
];

Members:
LOAD ID, 
     Name, 
     ApplyMap('Map_MemberType',ID,'Unknown Member Type') as MemberType
FROM
[DataFile.xlsx]
(ooxml, embedded labels, table is Sheet1);

Good luck

[guya]

Many thx Jatish. Is there a way to directly assign the member type to an ID or group of ID in the script load without referring to an extra table.

[jatishqv · Accepted solution]

Mapping Tables is definitely the "right" way of doing it, but this will also work.

Members:
LOAD ID, 
     Name, 
     if(Match(ID,1,2),'Direct',
		if(Match(ID,3,4,5),'Indirect',
			if(ID=6,'ToSign',
				'Unknown Member Type'))) as MemberType
FROM
[DataFile.xlsx]
(ooxml, embedded labels, table is Sheet1);

[guya]

Brilliant! works perfectly.

Thx a lot.