Replace values in field with the load script

[abalert]

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

[tresesco]

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

[abalert]

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.

[tresesco · Accepted solution]

Try like:

Load
   *,
   If( Left(MemberID,2)= 'TS', 'Staff',
       if( Left(MemberID,3)= 'TTT' , 'NonMember', MemberID))    as NewGender
From <> ;

[abalert]

Really appreciated, that worked!

Now I know how to put two IF statements together, thank you Tresesco!