Apply Map to load the related data is not working in data load script

[klrameet]

Hi

I have loaded the file containing the underline data and i am adding a calculated field 'N_L' using some formula. Now I want to load another script to use this information to populate remaining rows from the file, which are blank and could not be populated as this is related data and can only be populated in iteration.

Data in File:

# Empid, Supervisorid

1 12334   34344               

2 34344   45455

3 45455   56555

4 56555  67666

Data loaded with calculated Field N_L

# Empid, Supervisorid   N_L

1 12334   34344                 N2L1               

2 34344   45455  

3 45455   56555

4 56555  67666

Expected Data using ApplyMap Iteration#1

# Empid, Supervisorid   N_L

1 12334   34344                 N2L1               

2 34344   45455                 N3L2

3 45455   56555

4 56555  67666

Expected Data using ApplyMap Iteration#2 

# Empid, Supervisorid   N_L

1 12334   34344                 N2L1               

2 34344   45455                 N3L2

3 45455   56555                 N4L3

4 56555  67666

Code:

File Load with Calculated Field 'N_L

if([EmpID] = '12334', 'N2L1', '') AS [N_L]
FROM [lib://AttachedFiles/1_Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

COde for ApplyMap Iteration 1:

MAP_Table_NL:
MAPPING LOAD
EmpID as [Supervisor ID],
if(EmpID = '12334', 'N2L1', ' '))) as N_L
FROM [lib://AttachedFiles/1_Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

HRIS_Data_2:
Load *,
ApplyMap('MAP_Table_NL',[Supervisor ID],'') as N_L
FROM [lib://AttachedFiles/G_Report.xlsx]
Where EmpID  ='' 
(ooxml, embedded labels, table is Feuil5);

eRROR being Faced:

The following error occurred:

Cannot open file: 'lib://AttachedFiles/1_Report.xlsx Where EmpID = '';

(Native Path: *** System error: ***)

The error occurred here:

HRIS_Data_2: Load *, ApplyMap('MAP_Table_NL',[Supervisor ID],'') as N_L FROM [lib://AttachedFiles/1_Report.xlsx] Where EmpID = ''

(ooxml, embedded labels, table is Feuil5)

[lironbaram · Accepted solution]

hi 

your script error 

is that you need to put the where line as the last one after the brackets

[albertovarela · Accepted solution]

Try this instead. 

HRIS_Data_2:
Load *,
ApplyMap('MAP_Table_NL',[Supervisor ID],'') as N_L
FROM [lib://AttachedFiles/G_Report.xlsx] (ooxml, embedded labels, table is Feuil5)
Where EmpID  ='' ;

[klrameet]

MAP_Table_NL:
MAPPING LOAD
EmpID as [Supervisor id],
if([Supervisor ID]='123345','N4/L5','') AS [N_L]
FROM [lib://AttachedFiles/1_Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

Table1:
Load
EmpID,
ApplyMap('MAP_Table_NL',[Supervisor ID],'')
FROM [lib://AttachedFiles/1_Report.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT Match(EmpID,'123345','123345') AND ([Supervisor ID] <>'123345');

Thanks @lironbaram  & @albertovarela .

My Script now looks like above but instead of updating same column with N4/L5, it has created another field called ApplyMap('MAP_Table_NL',[Supervisor ID],'').

Also, i want to go about updating N_L column for the remaining EmpIDs as N4/L6 for who's Supervisor are now L4/N5 from above script, any pointers would be helpful.

@sunny_talwar 

[albertovarela]

You need to alias the mapped field to a fieldname, for example:

Table1:
Load
EmpID,
ApplyMap('MAP_Table_NL',[Supervisor ID],'') as YourNewFieldName
FROM [lib://AttachedFiles/1_Report.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT Match(EmpID,'123345','123345') AND ([Supervisor ID] <>'123345');