Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I'm three weeks old for QlikView. I'm trying to LOAD data from ExcelSource and hopefully to get a result looks like TableAfter. It seems easy to do however still can't make it work from my Load Script, please advise way(s?) to achieve this, thanks a lot!
ExcelSource
MemberID | Gender | Item |
T00001 | F | Shoes |
T00002 | M | Tops |
TS0001 | M | Bottom |
TS0002 | M | Shoes |
TS0003 | Tops | |
T00007 | F | Bottom |
TTT888 | Bottom | |
TTT888 | Shoes |
TableAfter
MemberID | GenderNew | Item |
T00001 | F | Shoes |
T00002 | M | Tops |
TS0001 | Staff | Bottom |
TS0002 | Staff | Shoes |
TS0003 | Staff | Tops |
T00007 | F | Bottom |
TTT888 | NA | Bottom |
TTT888 | NA | Shoes |
Try like:
Load
*,
If( Left(MemberID,2)= 'TS', 'Staff',
if( Left(MemberID,3)= 'TTT' , 'NonMember', MemberID)) as NewGender
From <> ;
What is logic of deriving the new gender column? May be like:
- If( member id starts with 'TS', 'Staff',
(if starts with 'TTT' , 'NA', as it is)) ??
I think only two simple rules should apply, thank you Tresesco!
member id starts with 'TS', 'Staff'
member id starts with 'TTT', 'NonMember' //I think using the word NonMember will be better.
Try like:
Load
*,
If( Left(MemberID,2)= 'TS', 'Staff',
if( Left(MemberID,3)= 'TTT' , 'NonMember', MemberID)) as NewGender
From <> ;
Really appreciated, that worked!
Now I know how to put two IF statements together, thank you Tresesco!