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
sfatoux72
Partner - Specialist
Partner - Specialist

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;

Not applicable
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://masterssummit.com

http://qlikviewcookbook.com

Anonymous
Not applicable
Author

Thanks Rob and Sebastien

Anonymous
Not applicable
Author

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.

Not applicable
Author

Thank you so much for your comments

Not applicable
Author

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.

Anonymous
Not applicable
Author

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

;

Anonymous
Not applicable
Author

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.