Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: ApplyMap with LOAD INLINE not working

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.

31 Replies
deepaktibhe
Valued Contributor

Re: ApplyMap with LOAD INLINE not working

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

Re: ApplyMap with LOAD INLINE not working

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
Valued Contributor

Re: ApplyMap with LOAD INLINE not working

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

Re: ApplyMap with LOAD INLINE not working

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

Re: ApplyMap with LOAD INLINE not working

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
Contributor III

Re: ApplyMap with LOAD INLINE not working

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

Re: ApplyMap with LOAD INLINE not working

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.

Re: ApplyMap with LOAD INLINE not working

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
Honored Contributor III

Re: ApplyMap with LOAD INLINE not working

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

Community Browser