ApplyMap cannot find a field

[MikeGaunt1]

Hi All, 

So in the raw data I have two fields, "Plnt" and "MRPcn"

In the mapping load I needed to effectively concat these together .

MRPCNMAP:
Mapping LOAD * Inline
[
MRPLONG,MRP controller name
0600000, VENDOR STOCKING
]

So the 0600 is the Plnt field and the 000 is the MRPcn field

Now when I apply the map, it cannot find the field MRP long 

LOAD
    Plnt,
    Material,
    Exc.,
    Resch.date,
    "N",
    "E",
    "n",
    If(Len("N")<1,'Planned Order',If(Len("n")<1,'Production Order','Blank')) as PRODPLAN,
    MRPCn,
    ApplyMap('MRPCNMAP',MRPLONG,'Not Known') as MRPCNLONG,
    Plnt&MRPCn AS MRPLONG,
    PGr,
   ApplyMap('PURCHAINGGROUPMAP',PGr,'Not Known') as PGrLong,
    SPT,
    ProcType,
    "MRP date",
    Typ
FROM [lib://DataFiles/Exception Message Raw Data.XLSX]
(ooxml, embedded labels, table is Sheet1);

Any Idea how I can get this to work.

[Chirantha]

Hey Mike,

Maybe the issue is that the ''MRPLONG'' field is not created until after the ''ApplyMap'' function is called. You should perhaps create the ''MRPLONG'' field before calling the ''ApplyMap'' function. Please try this and see if it helps

LOAD
   Plnt,
   Material,
   Exc.,
   Resch.date,
   "N",
   "E",
   "n",
   If(Len("N")<1,'Planned Order',If(Len("n")<1,'Production Order','Blank')) as PRODPLAN,
   MRPCn,
   Plnt&MRPCn AS MRPLONG,
   ApplyMap('MRPCNMAP',MRPLONG,'Not Known') as MRPCNLONG,
   PGr,
  ApplyMap('PURCHAINGGROUPMAP',PGr,'Not Known') as PGrLong,
   SPT,
   ProcType,
   "MRP date",
   Typ
FROM [lib://DataFiles/Exception Message Raw Data.XLSX]
(ooxml, embedded labels, table is Sheet1);
 

[udit_k]

use preceding load and try apply map:-

Load *,
 ApplyMap('MRPCNMAP',MRPLONG,'Not Known') as MRPCNLONG1;
LOAD
    Plnt,
    Material,
    Exc.,
    Resch.date,
    "N",
    "E",
    "n",
    If(Len("N")<1,'Planned Order',If(Len("n")<1,'Production Order','Blank')) as PRODPLAN,
    MRPCn,
    ApplyMap('MRPCNMAP',MRPLONG,'Not Known') as MRPCNLONG,
    Plnt&MRPCn AS MRPLONG,
    PGr,
   ApplyMap('PURCHAINGGROUPMAP',PGr,'Not Known') as PGrLong,
    SPT,
    ProcType,
    "MRP date",
    Typ
FROM [lib://DataFiles/Exception Message Raw Data.XLSX]
(ooxml, embedded labels, table is Sheet1);

[Or · Accepted solution]

@udit_k 's suggestion will work, but I would personally prefer simply referring to the fields directly in the ApplyMap() function to adding a preceding load for this specific purpose:

ApplyMap('MRPCNMAP',Plnt&MRPCn,'Not Known') as MRPCNLONG

Note that you can't refer to a field that was created within the load script inside the same load script, so you need to either use a preceding load, or refer to the underlying fields/formulas.