Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator III
Creator III

Join Data for missing values

I have a table

 

Table A:

NameID
A1
B 
C5
D 
E7

 

Table B:

NameID
A1
B2
C0
D9
E7
F8
I10
J11
K15

 

Result:

NameID
A1
B2
C5
D9
E7

 

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

Labels (2)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob

 

View solution in original post

9 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

qlikwiz123
Creator III
Creator III
Author

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

PrashantSangle

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

 

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
qlikwiz123
Creator III
Creator III
Author

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.

PrashantSangle

ok in such case you can applymap() ..

Check below thread

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

 

Regards,

Max

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob

 

qlikwiz123
Creator III
Creator III
Author

Perfect! Thank you so much @rwunderlich  and @PrashantSangle 

qlikwiz123
Creator III
Creator III
Author

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)

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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/Mappi...

-Rob