Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
The best way to this is use a mapping table and ApplyMap. It fundamentally works like a vlookup in excel.
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
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);
Hi,
The best way to this is use a mapping table and ApplyMap. It fundamentally works like a vlookup in excel.
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
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.
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);
Brilliant! works perfectly.
Thx a lot.