topic Re: Can you apply map on fields created from previous apply maps? in QlikView

Can you apply map on fields created from previous apply maps?

Tue, 17 Nov 2015 18:48:24 GMT

Hello, I am wracking my head over this one:

I am trying to create a THIRD apply map script based on two successful fields created similarly from original fields but the third apply map doesn't seem to work/recognize the other two. Is there a work around or am I doing something incorrectly?

Mapping_CC:

Mapping LOAD

[Department],

[MPR Dept]

FROM [\\ANALOG\USERS\WILM\kgulman\Desktop\Qlikview\Bryan Liz Project\Mapping\Table.xlsx]

(ooxml, embedded labels, table is Sheet3);

Mapping_AdHoc:

Mapping LOAD

[Concatenated],

[Ad Hoc Flash]

FROM [\\ANALOG\USERS\WILM\kgulman\Desktop\Qlikview\Bryan Liz Project\Mapping\Table.xlsx]

(ooxml, embedded labels, table is Sheet3);

LOAD [Cost Ctr],

[CO Area],

CONCATENATE,

Department,

SPENDTYPE,

[Std Hier.],

ApplyMap('Mapping_Site', [Std Hier.]) As [Local Site],

ApplyMap('Mapping_CC', [Department]) As [MPR Dept]

// ApplyMap('Mapping_AdHoc', ([Local Site]&[MPR Dept]),ApplyMap('Mapping_AdHoc',[MPR Dept])) As [Ad Hoc]

Re: Can you apply map on fields created from previous apply maps?

sunny_talwar — Tue, 17 Nov 2015 19:55:20 GMT

May be do it in the preceding load:

LOAD *,

ApplyMap('Mapping_AdHoc', ([Local Site]&[MPR Dept]),ApplyMap('Mapping_AdHoc',[MPR Dept])) As [Ad Hoc];

LOAD [Cost Ctr],

[CO Area],

CONCATENATE,

Department,

SPENDTYPE,

[Std Hier.],

ApplyMap('Mapping_Site', [Std Hier.]) As [Local Site],

ApplyMap('Mapping_CC', [Department]) As [MPR Dept]

UPDATE: the issue is that you were trying to use newly created field names ([Local Site] and [MPR Dept]) in the same resident load which isn't possible. Move it to the preceding load and you just be fine.

Re: Can you apply map on fields created from previous apply maps?

jolivares — Tue, 17 Nov 2015 19:57:43 GMT

Yes you can nest the functions Applymap, but in your case you have a recursive call of the function. I don't know if you can do that, but you can create a Map1_AdHoc and Map2_AdHoc.

Try this...

Re: Can you apply map on fields created from previous apply maps?

Tue, 17 Nov 2015 20:17:11 GMT

I think I get where you are getting at, but the above is still giving me trouble, nothing seems to come up

Re: Can you apply map on fields created from previous apply maps?

Tue, 17 Nov 2015 20:20:37 GMT

Nevermind worked like a charm!

Re: Can you apply map on fields created from previous apply maps?

sunny_talwar — Tue, 17 Nov 2015 20:24:18 GMT

Try adding two mapping load tables like Juan Olivares‌ mentioned and see if that helps:

LOAD *,

ApplyMap('Mapping_AdHoc1', ([Local Site]&[MPR Dept]),ApplyMap('Mapping_AdHoc2',[MPR Dept])) As [Ad Hoc];

LOAD [Cost Ctr],

[CO Area],

CONCATENATE,

Department,

SPENDTYPE,

[Std Hier.],

ApplyMap('Mapping_Site', [Std Hier.]) As [Local Site],

ApplyMap('Mapping_CC', [Department]) As [MPR Dept]

Re: Can you apply map on fields created from previous apply maps?

Mark_Little — Tue, 17 Nov 2015 20:28:16 GMT

Sunny's approach is correct and the way i would always tackle multiple applymap instead of nesting them.

If it is bring nothing then without the data it is hard to say what is going on, you will need to trouble shoot the problem and see where it is going wrong.

Add a fail condition to your apply map like below.

ApplyMap('Mapping_Site', [Std Hier.],[Std Hier.]&'|NoMap').

once you no where it is failing it will be easier to help

Mark