Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
Usually I don't struggle with the ApplyMap function, has been working great. However, when I try to use it with an INLINE lookup such as the example below, it will not map to AAContactNoDesc. Am I doing something wrong here?
MAP_AACONTACTNO:
MAPPING LOAD * INLINE
[AAContactNumber, AAContactNoDesc
'0113 438 0120','Flying to Mars'
'0330 780 7436','Flying to the Moon'];
LOAD Letter_code,
DELIVERY_NNA,
ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,
LoadedDate
FROM [\\abcdef.qvd] (qvd);
Many thanks, Alex
Finally solved this problem. Thanks to this post from Henric https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/30/data-cleansing where the notion of MapSubstring is suggested as an alternative.
The final code looks like this, where the text() function is doing its casting job.
MAP_AACONTACTNO:
LOAD * INLINE
[AAContactNumber, AAContactNoDesc
'0113 438 0120','Flying to Mars'
'0330 780 7436','Flying to the Moon'];
MAP_AACONTACTNO2:
MAPPING LOAD
text(left(AAContactNumber,14)) as AAContactNumber,
AAContactNoDesc
RESIDENT MAP_AACONTACTNO;
LOAD Letter_code,
DELIVERY_NNA,
MapSubString('MAP_AACONTACTNO2',text(left(AAContactNumber,14)) as AAContactNoDesc,
LoadedDate
FROM [\\abcdef.qvd] (qvd);
Thanks to all those helping to solve this one, much appreciated, Alex.
Try
MAP_AACONTACTNO:
MAPPING LOAD * INLINE
[AAContactNumber, AAContactNoDesc
'0113 438 0120','Flying to Mars'
'0330 780 7436','Flying to the Moon'];
LOAD Letter_code,
DELIVERY_NNA,
ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,
LoadedDate
FROM [\\abcdef.qvd] (qvd);
I think your mapping is set up wrong, try this:
MAPPING LOAD
AAContactNumber, AAContactNoDesc
INLINE [
AAContactNumber, AAContactNoDesc
0113 438 0120,Flying to Mars
0330 780 7436,Flying to the Moon];
and remove the extra ) in ApplyMap('MAP_AACONTACTNO',AAContactNumber)) as AAContactNoDesc,
By the way, good debugging practise is to make use of the third parameter that ApplyMap uses. So instead of
ApplyMap('MAP_AACONTACTNO',AAContactNumber)
You use
ApplyMap('MAP_AACONTACTNO',AAContactNumber, 'Unknown') so that anything unmapped will show up and you know you missed something.
EDIT: Had to add the AAContactNumber, AAContactNoDesc also to the inline part. sorry
MAP_AACONTACTNO:
MAPPING LOAD * INLINE
[AAContactNumber, AAContactNoDesc
'0113 438 0120','Flying to Mars'
'0330 780 7436','Flying to the Moon'];
LOAD Letter_code,
DELIVERY_NNA,
ApplyMap('MAP_AACONTACTNO',AAContactNumber)) as AAContactNoDesc,
LoadedDate
FROM [\\abcdef.qvd] (qvd);
remove extra bracket at end of the apply map
Thanks Deepak, that was indeed a typo, which I'll edit out. I did try re-running though just in case, and the behaviour is that AAContactNoDesc is shown with the number in it, not the description as one would expect.
Thanks Coen,
I tried it without the single quote and use the 3rd parameter to bucket the unmapped which is useful thanks. However, instead of getting all telephone numbers back, I am now getting UNKNOWN for everything.
ApplyMap('MAP_AACONTACTNO',AAContactNumber,'UNKNOWN') as AAContactNoDesc,
Is this a data type issue possible? The join key is clearly the contact no, is QV treating it as text or a number? Should I force it to be text like text(AAContactNumber) to ensure the join/mapping works? or?
Alex
Alex ApplyMap() is working with one filed which is having same filed name in both table.
in your table
MAP_AACONTACTNO:
MAPPING LOAD * INLINE
[AAContactNumber, AAContactNoDesc
'0113 438 0120','Flying to Mars'
'0330 780 7436','Flying to the Moon'];
LOAD Letter_code,
DELIVERY_NNA,
ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,
LoadedDate
FROM [\\abcdef.qvd] (qvd);
try this
MAP_AACONTACTNO:
MAPPING LOAD * INLINE
[AAContactNumber, AAContactNoDesc
'0113 438 0120','Flying to Mars'
'0330 780 7436','Flying to the Moon'];
LOAD Letter_code,
DELIVERY_NNA,
AAContactNumber,
ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,
LoadedDate
FROM [\\abcdef.qvd] (qvd);
Thanks Ahmar, I tried this mod and it does not affect the outcome. I also tried a field alias and re-inserting the single quotes. Still not getting the description to come through though. At the moment, it is still only displaying -
AAContactNoDesc
UNKNOWN
for all values despite the fact that the numbers are the same and the description should be coming through.
Can you share a snapshot of a list box of AAContactNumber field? To me it seems formatting difference between applymap field and the actualy table field.
Not sure though , Do you have AAContactNumber in [\\abcdef.qvd], if yes than check if values are matching with your inline map table values.