topic Re: How to create Calculated field based on excel file in QlikView

How to create Calculated field based on excel file

Anonymous — Sat, 13 Feb 2016 02:11:36 GMT

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

Re: How to create Calculated field based on excel file

sfatoux72 — Sat, 13 Feb 2016 03:05:05 GMT

‌

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;

Re: How to create Calculated field based on excel file

Sat, 13 Feb 2016 03:55:44 GMT

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.

Re: How to create Calculated field based on excel file

rwunderlich — Sat, 13 Feb 2016 05:55:59 GMT

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

Re: How to create Calculated field based on excel file

Anonymous — Sat, 13 Feb 2016 06:23:17 GMT

Thanks Rob and Sebastien

Re: How to create Calculated field based on excel file

Anonymous — Wed, 17 Feb 2016 03:43:19 GMT

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.

Re: How to create Calculated field based on excel file

Wed, 17 Feb 2016 10:23:06 GMT

Thank you so much for your comments

Re: How to create Calculated field based on excel file

Wed, 17 Feb 2016 12:36:41 GMT

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.

Re: How to create Calculated field based on excel file

Anonymous — Tue, 23 Feb 2016 06:02:49 GMT

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],

FROM

source;

Final:

Load*,

if(Vendor = 'other',[String Task],Vendor) as VendorX

Resident Orders;

drop table Orders;

LOAD *,

$(vMapExpr([String Task])) as Vendor

;

Re: How to create Calculated field based on excel file

Anonymous — Tue, 23 Feb 2016 06:05:40 GMT

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.

Re: How to create Calculated field based on excel file

Anonymous — Tue, 23 Feb 2016 06:08:25 GMT

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

Re: How to create Calculated field based on excel file

Anonymous — Tue, 23 Feb 2016 06:13:54 GMT

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

Re: How to create Calculated field based on excel file

PradeepReddy — Tue, 23 Feb 2016 06:57:29 GMT

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
];

Re: How to create Calculated field based on excel file

sfatoux72 — Tue, 23 Feb 2016 09:03:10 GMT

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

Re: How to create Calculated field based on excel file

Tue, 23 Feb 2016 13:36:40 GMT

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

Re: How to create Calculated field based on excel file

Thu, 25 Feb 2016 12:22:35 GMT

Hi QlikDash,

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

Thanks,
Sreeman

Re: How to create Calculated field based on excel file

Anonymous — Fri, 26 Feb 2016 05:01:08 GMT

Sreeman,

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

Thanks again