topic Re: Replace data of a table with data from another table in QlikView

Replace data of a table with data from another table

Antoine553 — Sat, 16 Nov 2024 17:55:22 GMT

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?

Re: Replace data of a table with data from another table

dplr-rn — Mon, 26 Oct 2020 14:26:32 GMT

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;

Re: Replace data of a table with data from another table

Taoufiq_Zarra — Mon, 26 Oct 2020 14:31:19 GMT

@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 ];

Re: Replace data of a table with data from another table

Antoine553 — Mon, 26 Oct 2020 15:30:18 GMT

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.

Re: Replace data of a table with data from another table

Kushal_Chawda — Mon, 26 Oct 2020 16:02:09 GMT

@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;