topic Re: Adding a new column with value based on value of other column in App Development

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

guya — Tue, 06 Aug 2019 13:49:34 GMT

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

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

jatishqv — Tue, 06 Aug 2019 14:23:24 GMT

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/ScriptPrefixes/Mapping.htm

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

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

guya — Tue, 06 Aug 2019 14:46:18 GMT

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.

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

jatishqv — Tue, 06 Aug 2019 14:54:27 GMT

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);

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

guya — Tue, 06 Aug 2019 15:05:30 GMT

Brilliant! works perfectly.

Thx a lot.