topic Re: Replace values in field with the load script in QlikView

Replace values in field with the load script

abalert — Sat, 16 Nov 2024 01:50:24 GMT

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

Re: Replace values in field with the load script

tresB — Wed, 27 Nov 2019 07:20:10 GMT

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

Re: Replace values in field with the load script

abalert — Wed, 27 Nov 2019 07:41:30 GMT

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.

Re: Replace values in field with the load script

tresB — Wed, 27 Nov 2019 07:51:46 GMT

Try like:

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

Re: Replace values in field with the load script

abalert — Wed, 27 Nov 2019 08:01:26 GMT

Really appreciated, that worked!

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