Replace data of a table with data from another table

[Antoine553]

Hello

I'm loading one table from an xlsx file wich contains some peoples with their number, name, job and Serv_Name. However their Serv_Name is a false one (always the same) if their job is Director.

N°	Name	Job	Serv_Name
1563	Jack	Commercial	52100
2412	John	Director	02100
3786	Blerg	IT	15000
4321	Test	Director	02100

To get the right one, I have to get it from another file looking like this one.

N°	Serv_Name
2412	02898
4321	02497

So my question is, how can i create a single table in the Script from these two while changing the Serv_Name with the right one if Job='Director' please?

[dplr-rn]

You can use a map.

Load table 2 into a map

like so

ServMap:
mapping load  
N, Serv_Name
from source;

then use apply map soemthing like below to get the  value

load  
N
,Name	
,Job
, ApplyMap('ServMap',N,Serv_Name) //If not present in map use value from table
from 
source;

[Taoufiq_Zarra]

@Antoine553 

Mapserver:

mapping  
LOAD * INLINE [
    N°, Serv_Name
    2412, 02898
    4321, 02497
];


Data:
LOAD  N°, Name, Job, if(Job='Director',applymap('Mapserver',[N°]),Serv_Name) as Serv_Name INLINE [
    N°, Name, Job, Serv_Name
    1563, Jack, Commercial, 52100
    2412, John, Director, 02100
    3786, Blerg, IT, 15000
    4321, Test, Director, 02100
];

[Antoine553]

Thank for your reply

I might have oversimplified my case. Mapping can only work with two value (if i understood well) But what if i have an additionnal field years like this ?

    N°, Serv_Name, YEAR
    2412, 02898    2020
    4321, 02497    2020
    2412, 04898    2019
    4321, 04497    2019



    N°, Name, Job, Serv_Name, YEAR

    1563, Jack, Commercial, 52100, 2020
    2412, John, Director, 02100, 2020
    3786, Blerg, IT, 15000, 2020
    4321, Test, Director, 02100, 2020
    1563, Jack, Commercial, 52100, 2019
    2412, John, Director, 02100, 2019
    3786, Blerg, IT, 15000, 2019
    4321, Test, Director, 02100, 2019

Sry for the delay i fought with the forum to try and make clean html tables but it seems not to like it.

[Kushal_Chawda · Accepted solution]

@Antoine553  try below

Map_serve_name:
mapping load 
N°&'|'&YEAR as Key,
Serv_Name
FROM table;

Data:
LOAD N°, 
     Name, 
     Job, 
     if(Job='Director', applymap('Map_serve_name',N°&'|'&YEAR)) as Serv_Name, 
     YEAR
FROM DataTable;