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
Here you go. After applying the text() function it looks like they are loading as the same data type now.
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?
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...?
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.
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)?
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
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);
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.
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.
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