topic Wildcard when ApplyMap in QlikView

Wildcard when ApplyMap

Mon, 06 Feb 2012 12:34:54 GMT

Hi!

I'm mapping serial numbers to a description and I just wonder if there are any way to create a sequence with a wildcard?

Six digits and the seventh is 0-9.

This is what I have now:

mytable:

Mapping LOAD * INLINE [

QLKRTP, QLDESC_OWN

4263870,Lorem ipsum

4263871,Lorem ipsum

4263872,Lorem ipsum

4263873,Lorem ipsum

4263874,Lorem ipsum

4263875,Lorem ipsum

4263876,Lorem ipsum

4263877,Lorem ipsum

4263878,Lorem ipsum

4263879,Lorem ipsum

4263880,Dolor sit amet

4263881,Dolor sit amet

4263882,Dolor sit amet

4263883,Dolor sit amet

4263884,Dolor sit amet

4263885,Dolor sit amet

4263886,Dolor sit amet

4263887,Dolor sit amet

4263888,Dolor sit amet

4263889,Dolor sit amet

];

But I want:

mytable:
Mapping LOAD * INLINE [
QLKRTP, QLDESC_OWN
426387*,Lorem ipsum 
426388*,Dolor sit amet
];

Possible?

regd

thomas

Wildcard when ApplyMap

swuehl — Mon, 06 Feb 2012 12:43:25 GMT

Define your map table like

mytable: Mapping LOAD * INLINE [ QLKRTP, QLDESC_OWN 426387,Lorem ipsum 426388,Dolor sit amet ];

and then use

applymap('mytable', left(FIELD,6) )

in your load

Wildcard when ApplyMap

Mon, 06 Feb 2012 13:45:46 GMT

Hi!

Need

applymap('mytable', left(FIELD,7) ) and that's why I need 0-9 on every possible 6-digit number.

Sometimes , when certain conditions is met, I need to do a deeper investigation and look in a different mapping table.

rgds

thomas

Wildcard when ApplyMap

swuehl — Mon, 06 Feb 2012 13:56:46 GMT

Thomas,

I understood that you have a field with a 7 digit number, but only the first 6 are significant for your lookup.

So I suggest that you look only at your first 6 digits and look those up in your mapping table.

I quite don't understand when you are saying:

Need applymap('mytable', left(FIELD,7) ) and that's why I need 0-9 on every possible 6-digit number.

or why my suggestion should not solve your problem. Could you maybe give an example where my suggested code doesn't give you the expected result?

Re: Wildcard when ApplyMap

rwunderlich — Mon, 06 Feb 2012 15:30:38 GMT

It looks to me like Stefan's suggestion works in this case. However, if you need full wildcard mapping, see the QV Cookbook (http://robwunderlich.com/downloads/) example "Mapping with a table using wildcards" or easier still use the Qlikview Components (http://qlikviewcomponents.org) function "Qvc.CreateWildMapExpression".

-Rob

Re: Wildcard when ApplyMap

Mon, 06 Feb 2012 19:05:15 GMT

If I understand you maybe this can help you:

map_table:
Mapping
LOAD '|' & QLKRTP as QLKRTP_Map, QLDESC_OWN as QLDESC_OWN_Map INLINE [
QLKRTP, QLDESC_OWN
426387,Lorem ipsum
426388,Dolor sit amet
];
DATA:
LOAD purgechar(mapsubstring('map_table','|' & ID),'0123456789') as MappedField, * INLINE [
ID, VALUE
426387023432,TypeA
426387123232,TypeA
426387229564,TypeA
426388102932,TypeB
426388923983,TypeB
];

Regards

Re: Wildcard when ApplyMap

Mon, 31 Mar 2014 12:54:06 GMT

Hi Rob,

Is this solution (createwildmap OR the Cookbook - they appear to be the same thing as per the script) working in QV11?

I get an error on the first line, seems like there's something missing. I tried removing the ( ' ) before 'pick' but that leads to many more syntax checks.

Can you please guide. Appreciate all your help.

-Chirag

Re: Wildcard when ApplyMap

srini — Tue, 23 Apr 2019 11:08:35 GMT

Hi Swuehl,

I have tried this in my load but it is not working ....

My code is:

Maptable:
Mapping Load * Inline
[
OBS Unit PathMapping-FROM,OBS Unit PathMapping-TO
ALL/HOST/IT/RBWM Technology/RBWM Sales & Distribution IT (RTN08184)/S&D Development (RTN08187)/HSDI_47230,Sales & Distribution
ALL/HOST/IT/RBWM Technology/RBWM SSP (RTN09803)/RBWM SSP (RTN07723)/HGSU_301419,SSP
ALL/HOST/IT/RBWM Technology/RBWM Sales & Distribution IT (RTN08184)/Self-Service (RTN06457)/HTSU_39997644,Sales & Distribution
ALL/HOST/IT/RBWM Technology/RBWM Sales & Distribution IT (RTN08184)/Self-Service (RTN06457)/HGSU_301070,Sales & Distribution
ALL/HOST/IT/RBWM Technology/RBWM Retail Business Banking IT (RTN08201)/RBB - IT (RTN08204)/HGMS_301391,RBB
ALL/HOST/IT/RBWM Technology/RBWM Retail Business Banking IT (RTN08201)/RBB - IT (RTN08204)/HGSU_301386,RBB
ALL/HOST/IT/RBWM Technology/RBWM Global Standards IT (RTN07235)/RBWM Global Standards IT (RTN06459)/HSDI_81338,Global Standards
ALL/HOST/IT/RBWM Technology/RBWM Sales & Distribution IT (RTN08184)/S&D Regional Development (RTN08191)/HBMEARE_720-730-57,Sales & Distribution
ALL/HOST/IT/RBWM Technology/RBWM Retail Business Banking IT (RTN08201)/RBB - IT (RTN08204)/HSDC_8136701,RBB
ALL/HOST/IT/RBWM Technology/RBWM Global Standards IT (RTN07235)/RBWM Global Standards IT (RTN06459)/HGSU_301071,Global Standards
];

RawData:
LOAD
"Resource Code",
Resource,
"Resource Manager",
Status,
"Hours",
// Mid([OBS Unit Path], 33, 24)as [OBS Unit Path],
// applymap('Maptable', Mid([OBS Unit Path],33) ),
// APPLYMAP( 'Maptable', [OBS Unit Path]) AS [OBS Unit Path],
APPLYMAP( 'Maptable', left([OBS Unit Path],48)),
"Billable Resource?",
"Week",
Email
FROM [lib://Clarity/RBWM Exceptions Report *.xlsx]
(ooxml, embedded labels, table is [Raw Data])
WHERE WildMatch ("OBS Unit Path",'*Sales & Distribution*','*Global Standards*','*SSP*','*Retail Business*');