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 |
I use your to table as Table1 and Table2.
WIth this solution, you need to add the plural : Oranges. Fruit
TableResult:
Load ID,
SubField( Upper( PurgeChar( [String Task]), ',.') , ' ') as wordTmp
Resident Table1;
inner join(TableResult)
Load Upper([Field A]) as wordTmp,
[Field B] as Result
Resident Table2;
remove field wordTmp;
Hi,
You can do by using the cartesian product of these 2 tables and check whether the value exists or not. Please see the below and you will get the value.
input:
load * 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
];
outer join
temp:
load * inline [
Field A, Field B
Orange, Fruit
mint, plant
dog, animal
];
result:
load [String Task] as ss,
ID as i1,
if(wildmatch(Upper([String Task]),'*'&Upper([Field A])&'*')>0,[Field B],'') as Res
Resident input;
finalresult:
load ss as string_task,
i1 as id1,
Res as Result
Resident result where len(trim(Res))>0;
Thanks,
Sreeman.
See if this example gives you what you want.
Qlikview Cookbook: Indexing Keywords in Text http://qlikviewcookbook.com/recipes/download-info/indexing-keywords-in-text/
-Rob
Thanks Rob and Sebastien
Sreeman,
Thanks so much for your Reply. Although your solution gives correct results, the cartesian join will add too many rows and loading time will also be very long(i in fact tried it). so i cant implement it. but your idea is precious. It allowed me to think differently.
Thank you so much for your comments
Hi,
Instead of cross Join we can do by using inner join. And I am getting the required output. Please let me know if it is fine.
input:
load * 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
];
cal:
load [String Task] as string_task,
ID as id1,
WildMatch(Upper([String Task]),'*ORANGE*','*MINT*','DOG*') as [Field A]
Resident input;
join
temp:
load * inline [
Field A, Field B
1, Fruit
2, plant
3, animal
];
Thanks,
Sreeman.
Pls see the script below.
Thanks to Rob Wunderlich, I got my script working. However can someone please explain how is index field and mapping of variable
working to match the correct 'Search' field?
Summary: I want to maintain an excel file with 2 fields Search and Result. If [String Task] from Orders table contain a value that "wildmatches" with Search, I want it to be labeled Result. I understand pick and wildmatch is the right approach but how exactly is it mapping the content of the string with FieldIndex and variable vMapExpr
(note.. For those String Task that did not have an assigned Result label, I need it to be left as String Task. To achieve this I am using an If condition, if you have better suggestion, pls advice. I will really appreciate
wildmap:
LOAD Search,
Result
FROM
source;
_MapExpr:
LOAD
'pick(wildMatch($1,' & chr(39)
& concat(Search, chr(39) & ',' & chr(39), FieldIndex('Search', Search))
& chr(39)
& '), ' & chr(39)
& concat(Result, chr(39) & ',' & chr(39), FieldIndex('Search', Search))
& chr(39) & ')' as MapExpr
RESIDENT wildmap
;
LET vMapExpr = peek('MapExpr', -1);
DROP TABLES _MapExpr,wildmap;
Orders:
LOAD *,
$(vMapExpr([String Task])) as Vendor
;
LOAD [String Task],
ID
FROM
source;
Final:
Load*,
if(Vendor = 'other',[String Task],Vendor) as VendorX
Resident Orders;
drop table Orders;
LOAD *,
$(vMapExpr([String Task])) as Vendor
;
sreeman, in this expn:-----------WildMatch(Upper([String Task]),'*ORANGE*','*MINT*','DOG*'), you are hardcoding the values of spring task. I want this to be dynamic as the values will be maintained externally in excel sheet.
so this appraoch, I can not take
Thanks.