Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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.
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.
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...
I think I get where you are getting at, but the above is still giving me trouble, nothing seems to come up
Nevermind worked like a charm!
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]
Hi
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