Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to create Calculated field based on excel file

String TaskID
The oranges in the trees were growing1001
mint, herb, basil are my kinda thing1002
dogs, cats and parrots make great pets1003

I have the 2 above fields Spring Task and ID, I want to achieve the following:

if the field contains text *orange*, I want a value Fruit under a new calculated field , Result.

similar, I want Results, plant for *herb*, Animal for *dog* etc..

I know I can do this by if(wildmatch([String Task],'*orange*'>0, 'Fruit') as Result

Instead I want to maintain an excel file where I can store values for search and result

as below. so that the parameters in wildmatch can be substituted by below table. how can I do this. May be a lookup table or minstring.

any help will be greatly appreciated/ Much ThanksHow

Field AField B
OrangeFruit
mintplant
doganimal
16 Replies
Anonymous
Not applicable
Author

Sebastien, this task requires to consider both orange and oranges. so we nee a wildmatch functionality. otherwise your soln would have solved it. Thanks for your suggestion

Anonymous
Not applicable
Author

here are the tables used and the output to help understand the question and the answer:

VendorX.PNGsearch result.PNG

PradeepReddy
Specialist II
Specialist II

Try something like this...

Map_Catg:

mapping

Load * inline

[

Field A, Field B

1, Fruit

2, plant

3, animal

];

Excel_Data:

Load *,

applymap('Map_Catg',wildmatch([String Task],'*Orange*','*mint*','*dog*'),'Others') as Category

inline

[

String Task, ID

"The oranges in the trees were growing", 1001

"mint, herb, basil are my kinda thing", 1002

"dogs, cats and parrots make great pets", 1003

"BMW, Petrole",1004

];

sfatoux72
Partner - Specialist
Partner - Specialist

You just need to add plural on your associative table to not use wild match. It must be more efficient.

Field AField B
OrangeFruit
mintplant
doganimal
OrangesFruit
mintsplant
dogsanimal
Not applicable
Author

Hi,

Kindly please look the attachment with the script and let me know whether this is the expecting. Because now I didn't hard code anything. Now i think you will expect the correct result. Please let me know if you have any issue.

Thanks,
Sreeman

Not applicable
Author

Hi QlikDash,

Did you see the attachment and let me know is this output you are expecting?

Thanks,
Sreeman

Anonymous
Not applicable
Author

Sreeman,

I got busy with dev other features. I will update you soon.

Thanks again