Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ApplyMap with LOAD INLINE not working

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

31 Replies
deepakqlikview_123
Specialist
Specialist

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

Not applicable
Author

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

qlikmsg4u
Specialist
Specialist

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

Not applicable
Author

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.

Not applicable
Author

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

ahmar811
Creator III
Creator III

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

Not applicable
Author

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.

sunny_talwar

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.

Digvijay_Singh

Not sure though , Do you have AAContactNumber in [\\abcdef.qvd], if yes than check if values are matching with your inline map table values.