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

31 Replies
Not applicable
Author

Here you go.  After applying the text() function it looks like they are loading as the same data type now.

sasiparupudi1
Master III
Master III

Hi Alex

Your data looks fine and the apply map works fine too..please see the attached.

are you using the correct mapping table name in your script?

Not applicable
Author

Hi Sasi, I agree it all looks correct and I've checked the mapping table name and that is correct.  Is anyone else thinking just try to give left join a go at this stage...?

maniram23
Creator II
Creator II

Hi,

This is use full for you. You can try this one.

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',Letter_code ) as AAContactNoDesc,

     LoadedDate

FROM [\\abcdef.qvd] (qvd);

When the data is same in AAContactNumber and  Letter_code that time u will get proper results.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

What is happening if you prefix all phone numbers with a non-numeric character, like '#'. Both in the INLINE table (or wherever your mapping table data is coming from) and in the second parameter of the Applymap call (e.g. '#' & AAContactNumber)?

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Hi,

the issue here is the datatype. I don't if it's been already said, but you can notice how QlikView treats data depending on the alignment of the field values. You can test this by creating a listbox containing AAContactNumber. By default QV aligns numbers to the right and text to the left. The issue is that the applymap is not finding any matches because the mapping table contains text (regardless that you're seeing numbers in your field values, QV is treating them as text because of the quotes) and your main table contains numbers treated... as numbers.

So the solution is either you define both fields as text or as numbers. Just take into account that if you want to keep the blank spaces between numbers you'll need to define/convert both AAContactNumber fields as text type.

Also this doesn't have to with using mapping or join to solve this. In general mappings are way more efficient than joins, but CANNOT be used when you have a many to many relationship,

hope this helps

regards

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

In your final table use preceding load as in

MAP_AACONTACTNO:

MAPPING LOAD * INLINE

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

'0330 780 7436','Flying to the Moon'];

Final_Table:

LOAD

    Letter_code,

     DELIVERY_NNA,

     ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

     LoadedDate

;

LOAD

    Letter_code,

     DELIVERY_NNA,

    //ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

     LoadedDate

FROM [\\abcdef.qvd] (qvd);

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.

Not applicable
Author

Upon further reflection, it seems the source data is actually loading with mixed data types i.e. the telephone numbers are being loaded as both numbers and text.  How is this possible?  Even the same number is being loaded twice, once as a number and once as text.

DataTypes.jpg

Peter_Cammaert
Partner - Champion III
Partner - Champion III

It's like you said in a previous post Re: ApplyMap with LOAD INLINE not working: data is loaded in different formats. When QV has a chance to discover a number in a text string, it will store a numerical value together with the text representation. The numerical value will always have precedence. This is the concept of dual() values which is central to qlikview. There is an easy way to verify this mixup: by default numerical values are right aligned, text strings are left aligned.

Usually this phenomenon is caused by loading field values from different sources. One source will propose them as text values, another one will make them look like numerical values. Only one problem: if you use them as key values, they will not match. If you use them in a list box, they will not map to the same underlying value (for comparisons).. they will be treated as separate values, although they may look identical (see your screenshot).

Everywhere you load phone numbers, force them to be treated as text by using the text() function. Text() will store a value as text-only and block the conversion to numerical values.

Or, as I suggested before, prefix all phone numbers with a non-numerical character like '#' when loading them from the source. This will block the interpretation - behind the scenes - by QV.

Best,

Peter