topic Re: Mapping using wildcard in QlikView

Mapping using wildcard

Anonymous — Tue, 03 Oct 2017 14:47:12 GMT

Hi all.

I am having some trouble to use the mapping load * inload using wildcard.

I have read a lot around the community and I have seen various recommendations. But I have not had any luck using Rob Wunderlicht's script on Cookbook (Mapping with Wildcards | Qlikview Cookbook) nor the use of "MapSubString" instead of "ApplyMap".

I will put the relevant information of my script and hopefully someone will be able to point me on the right direction.

// Load Priority mapping

PRIORITYMAP:

MAPPING LOAD * INLINE [

F1, F2

P1 < 10 ITEMS (PRIORITY URGENT), P1 < 10 ITEMS

P1 > 9 ITEMS (PRIORITY URGENT), P1 > 9 ITEMS

P2 (PRIORITY HIGH), P2

P3 (PRIORITY MEDIUM), P3

];

_TICKETS:

LOAD

APPLYMAP('PRIORITYMAP', UPPER(Priority), UPPER(Priority)) AS PRIORITY

FROM

[...\Tickets.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

There has been few changes on the way things are reported and the wording after the brackets keep changing regularly. At the moment, the PRIORITYMAP table is updated manually. However, I would like something more permanent that would use the wildcard for what it is inside the brackets.

There are quite a large number of options in the Priority field but not all of them are mapped.

Is there any option that in the map I can look for:

P1 < 10 ITEMS *, P1 < 10 ITEM

Thank you for your help.

Re: Mapping using wildcard

vishsaggi — Tue, 03 Oct 2017 15:41:39 GMT

You have 4 values in your Mapping LOAD table for F1 field. What about other values what you want to have for them. You asked can we have an option to map like

P1 < 10 ITEMS*

For this you can write like

MAPPING LOAD IF(WildMatch(F1, 'P1 < 10 ITEM*'), F1, F1) AS F1, F2 INLINE [

.....

];

Re: Mapping using wildcard

Anonymous — Tue, 03 Oct 2017 15:45:56 GMT

Hello Vishwarath,

For the other values not included on the Map Load, I would like to keep them as they are.

Re: Mapping using wildcard

vishsaggi — Tue, 03 Oct 2017 15:48:07 GMT

Yes if condition does that like if wilmatch() matches the condition it pulls values from F1 if not it gets other values from F1. That is the reason i left F1 in else condition.

IF(Wildmatch(....), F1, F1)

Re: Mapping using wildcard

rwunderlich — Tue, 03 Oct 2017 15:53:42 GMT

If you can post some sample data I'll look at why wildcard mapping is not working.

-Rob

Re: Mapping using wildcard

Anonymous — Tue, 03 Oct 2017 16:25:01 GMT

Please find attached a test file.

Thank you.

Best Regards.

Re: Mapping using wildcard

rwunderlich — Tue, 03 Oct 2017 17:02:17 GMT

If your rule is that you always want the text to the left of the brackets can you get away with:

Trim(subfield(PRIORITY, '(', 1))

-Rob

Re: Mapping using wildcard

Anonymous — Tue, 03 Oct 2017 19:38:08 GMT

Thank you very much for your help Rob.

This does the trick for what I need. I really appreciate your help.

Best Regards