Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Wildcard when ApplyMap

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

7 Replies
Highlighted
MVP
MVP

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

Highlighted
Not applicable

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

Highlighted
MVP
MVP

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?

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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

Highlighted
Not applicable

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

Untitled.png

Highlighted
Creator
Creator

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*');