Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
klrameet
Contributor III
Contributor III

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

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)

 

Labels (4)
2 Solutions

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hi 

your script error 

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

View solution in original post

albertovarela
Partner - Specialist
Partner - Specialist

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  ='' ;

View solution in original post

4 Replies
lironbaram
Partner - Master III
Partner - Master III

hi 

your script error 

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

albertovarela
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

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
Partner - Specialist
Partner - Specialist

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