Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 A | Field B |
Orange | Fruit |
mint | plant |
dog | animal |
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
here are the tables used and the output to help understand the question and the answer:
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
];
You just need to add plural on your associative table to not use wild match. It must be more efficient.
Field A | Field B |
Orange | Fruit |
mint | plant |
dog | animal |
Oranges | Fruit |
mints | plant |
dogs | animal |
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
Hi QlikDash,
Did you see the attachment and let me know is this output you are expecting?
Thanks,
Sreeman
Sreeman,
I got busy with dev other features. I will update you soon.
Thanks again