topic Re: ApplyMap with LOAD INLINE not working in QlikView

ApplyMap with LOAD INLINE not working

Wed, 23 Sep 2015 09:09:23 GMT

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

Re: ApplyMap with LOAD INLINE not working

deepakqlikview_123 — Wed, 23 Sep 2015 09:16:41 GMT

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

Re: ApplyMap with LOAD INLINE not working

Wed, 23 Sep 2015 09:18:00 GMT

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

Re: ApplyMap with LOAD INLINE not working

qlikmsg4u — Wed, 23 Sep 2015 09:19:37 GMT

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

Re: ApplyMap with LOAD INLINE not working

Wed, 23 Sep 2015 09:27:41 GMT

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

Wed, 23 Sep 2015 09:39:16 GMT

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

ahmar811 — Wed, 23 Sep 2015 09:41:21 GMT

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

Re: ApplyMap with LOAD INLINE not working

Wed, 23 Sep 2015 10:18:05 GMT

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

sunny_talwar — Wed, 23 Sep 2015 10:23:28 GMT

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

Digvijay_Singh — Wed, 23 Sep 2015 10:31:58 GMT

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

tobias_klett — Wed, 23 Sep 2015 10:35:39 GMT

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

Re: ApplyMap with LOAD INLINE not working

sunilkumarqv — Wed, 23 Sep 2015 11:23:52 GMT

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

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 /* check the field is there or not */

ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,

LoadedDate

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

Re: ApplyMap with LOAD INLINE not working

sasiparupudi1 — Wed, 23 Sep 2015 11:35:51 GMT

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:

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',Trim(AAContactNumber)) as AAContactNoDesc,

LoadedDate

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

hth

Sasi

Re: ApplyMap with LOAD INLINE not working

Wed, 23 Sep 2015 11:38:12 GMT

Hi Tobias,

Yes, it seems that QV is loading the data differently.

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

Wed, 23 Sep 2015 11:39:21 GMT

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

Wed, 23 Sep 2015 11:40:08 GMT

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

Re: ApplyMap with LOAD INLINE not working

sasiparupudi1 — Wed, 23 Sep 2015 11:48:51 GMT

may be try

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

Re: ApplyMap with LOAD INLINE not working

sunny_talwar — Wed, 23 Sep 2015 11:50:46 GMT

May be try forcing Text to both of them?

MAP_AACONTACTNO:

MAPPING LOAD Text(AAContactNumber) as AAContactNumber,

AAContactNoDesc

INLINE [

AAContactNumber, AAContactNoDesc

0113 438 0120, Flying to Mars

0330 780 7436, Flying to the Moon

];

LOAD Letter_code,

DELIVERY_NNA,

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

LoadedDate

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

Re: ApplyMap with LOAD INLINE not working

Wed, 23 Sep 2015 13:43:39 GMT

Thanks Sunny, tried that and still no joy.

Re: ApplyMap with LOAD INLINE not working

sasiparupudi1 — Wed, 23 Sep 2015 14:01:08 GMT

Please could you provide sample data?