31 Replies Latest reply: Sep 30, 2015 6:15 AM by Alex Berry

# 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:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,

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

Many thanks, Alex

• ###### Re: ApplyMap with LOAD INLINE not working

Try

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,

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

• ###### 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.

• ###### Re: ApplyMap with LOAD INLINE not working

I think your mapping is set up wrong, try this:

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

• ###### 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

• ###### Re: ApplyMap with LOAD INLINE not working

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO',AAContactNumber)) as AAContactNoDesc,

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

remove extra bracket at end of the apply map

• ###### Re: ApplyMap with LOAD INLINE not working

Alex ApplyMap() is working with one filed which is having same filed name in both table.

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,

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

try this

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

AAContactNumber,

ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,

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

• ###### 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.

• ###### 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.

• ###### Re: ApplyMap with LOAD INLINE not working

Hi, to check it load without "Mapping" and put all the Fields into a table box...

• ###### Re: ApplyMap with LOAD INLINE not working

Hi Tobias,

Any ideas how to get ensure they both the same. I presume the box on the left is a number and on the right it is text?  Is that right?

• ###### Re: ApplyMap with LOAD INLINE not working

is there mapping field  AAContactNumber in the second table if so then it work

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

AAContactNumber  /*  check the field is there or not */

ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,

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

• ###### Re: ApplyMap with LOAD INLINE not working

The field is there, but as per my response to Tobias, it looks like the data types are being loaded differently.

• ###### Re: ApplyMap with LOAD INLINE not working

May be there are spaces in your source string.. try trimming it, also please check if the source strings match to the strings in the mappping load.

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO',Trim(AAContactNumber)) as AAContactNoDesc,

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

hth

Sasi

• ###### Re: ApplyMap with LOAD INLINE not working

Thanks Sasi, I tried trim(text(x)) to no avail.

• ###### Re: ApplyMap with LOAD INLINE not working

may be try

ApplyMap('MAP_AACONTACTNO',Text(Trim(AAContactNumber))),'NA') as AAContactNoDesc,

• ###### Re: ApplyMap with LOAD INLINE not working

May be try forcing Text to both of them?

MAP_AACONTACTNO:

AAContactNoDesc

INLINE [

AAContactNumber, AAContactNoDesc

0113 438 0120, Flying to Mars

0330 780 7436, Flying to the Moon

];

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO', Text(AAContactNumber)) as AAContactNoDesc,

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

• ###### Re: ApplyMap with LOAD INLINE not working

Thanks Sunny, tried that and still no joy.

• ###### Re: ApplyMap with LOAD INLINE not working

Please could you provide sample data?

• ###### Re: ApplyMap with LOAD INLINE not working

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

• ###### Re: ApplyMap with LOAD INLINE not working

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?

• ###### Re: ApplyMap with LOAD INLINE not working

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...?

• ###### Re: ApplyMap with LOAD INLINE not working

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

• ###### Re: ApplyMap with LOAD INLINE not working

Hi,

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

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

DELIVERY_NNA, ! !

ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

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

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

• ###### Re: ApplyMap with LOAD INLINE not working

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

• ###### Re: ApplyMap with LOAD INLINE not working

Hi,

MAP_AACONTACTNO:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

Final_Table:

Letter_code,

DELIVERY_NNA,

ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

;

Letter_code,

DELIVERY_NNA,

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

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

• ###### 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:

[AAContactNumber, AAContactNoDesc

'0113 438 0120','Flying to Mars'

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

MAP_AACONTACTNO2:

text(left(AAContactNumber,14)) as AAContactNumber,

AAContactNoDesc

RESIDENT MAP_AACONTACTNO;

DELIVERY_NNA,

MapSubString('MAP_AACONTACTNO2',text(left(AAContactNumber,14)) as AAContactNoDesc,

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

Thanks to all those helping to solve this one, much appreciated, Alex.

• ###### Re: ApplyMap with LOAD INLINE not working

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.

• ###### Re: ApplyMap with LOAD INLINE not working

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

• ###### Re: ApplyMap with LOAD INLINE not working

Thanks Peter.

• ###### Re: ApplyMap with LOAD INLINE not working

And for those that have been following this post, it seems that the key function that really made a difference was KeepChar.  The data loading into AAContactNumber was formatted like 0800 123 1234, however, the spaces between the numbers were not being seen by QV as spaces.  Therefore, KeepChar filters out all non-numerics to leave a clean text string that could be mapped to a description.

MapSubString('MAP_AACONTACTNO2',text(keepchar(AAContactNumber,'0123456789'))) as AAContactNoDesc,

For reference, I was obviously just assuming that what appeared to be spaces were spaces and treated as spaces .

Thanks to KeepChar, I can now successfully map millions of calls to a description.