topic Re: Join Data for missing values in QlikView

Join Data for missing values

qlikwiz123 — Sat, 16 Nov 2024 00:41:26 GMT

I have a table

Table A:

Name	ID
A	1
B
C	5
D
E	7

Table B:

Name	ID
A	1
B	2
C	0
D	9
E	7
F	8
I	10
J	11
K	15

Result:

Name	ID
A	1
B	2
C	5
D	9
E	7

How do I join Table B with Table A to fill the missing 'ID' values in Table A? I tried Left Join but it is not working. Also tried Where Not Exist (ID).

I DON'T want to re-write the values that are already present in Table A, but only fill the missing values from Table B. So Left - Join is not ideal in this case

Re: Join Data for missing values

rwunderlich — Tue, 12 May 2020 18:45:26 GMT

A left join won't work because TableA already contains an ID column. There are a couple of solutions depending on the structure of your script. One straightforward way is to not load the ID field into TableA initially. Then a Left Join from TableB will work. This assumes that TableB has all the values you need, not just the missing ones.

-Rob

Re: Join Data for missing values

qlikwiz123 — Tue, 12 May 2020 21:19:52 GMT

Hi @rwunderlich

I need to keep ID field values in Table A as there might be instances where Table B may have different ID Field Values.

I only need to fill the gaps for the ID field in Table A. Please suggest assuming the script is simple as below.

Table_A:

Load Name, ID

From ...SourceA

Table B:

Load Name, ID

From....SourceB

Re: Join Data for missing values

PrashantSangle — Tue, 12 May 2020 21:31:47 GMT

hi,

as rob suggest left join not work because ID field already present in 1st table so either you have rename it or do not consider it.

try below

Table_A:

Load Name

From ...SourceA

Left Join

Load Name, ID

From....SourceB

Regards,

max

Re: Join Data for missing values

qlikwiz123 — Tue, 12 May 2020 21:34:53 GMT

Hi,

I don't want to replace the values of Field ID from Table A. Instead fill the gaps from Table_B. Doing a left Join will attach 'ALL' values from ID field in Table B, which should be avoided.

Re: Join Data for missing values

PrashantSangle — Tue, 12 May 2020 21:43:10 GMT

ok in such case you can applymap() ..

Check below thread

https://help.qlik.com/en-US/sense/April2020/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunctions/ApplyMap.htm

Regards,

Max

Re: Join Data for missing values

rwunderlich — Tue, 12 May 2020 22:31:23 GMT

NameIdMap:
Mapping Load Name, ID
From....SourceB

Table_A:
Load Name,
if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID
From ...SourceA

-Rob

Re: Join Data for missing values

qlikwiz123 — Wed, 13 May 2020 14:14:22 GMT

Perfect! Thank you so much @rwunderlich and @PrashantSangle

Re: Join Data for missing values

qlikwiz123 — Wed, 13 May 2020 20:16:00 GMT

Quick follow up question @rwunderlich

How can I leave the ID field blank in this expression if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID

if the Name is not found in SouceB (NameIdMap in this load)

Re: Join Data for missing values

rwunderlich — Thu, 14 May 2020 15:10:40 GMT

The optional third parameter to ApplyMap() is a default value. You can specify it there.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/MappingFunctions/ApplyMap.htm

-Rob