Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Table A:
Name | ID | No |
A | 1 | 9 |
B | ||
C | 5 | 77 |
D | ||
E |
Table B:
Name | ID | No |
B | 4 | 11 |
D | 6 | 23 |
E | 9 | 47 |
G | 45 | 6 |
H | 67 | 22 |
N | 3 | 85 |
How do I Join Table B with A so that I can fill all the missing ID and No values in Table A from Table B?
Mapping and Preceeding loads are failing as my tables coming from SQL queries.
Result:
Name | ID | No |
A | 1 | 9 |
B | 4 | 11 |
C | 5 | 77 |
D | 6 | 23 |
E | 9 | 47 |
Hi @qlikwiz123 , please check if something like this is a solution for your question :
Load
Name,
ID,
No
from B ;
concatenate //optional
Name,
ID,
No
from A
Where
not exists (Name, Name);
Hi @QFabian
Thank you. But doing so will bring all the rows from Table B which is not needed. I only need to fill the gaps in Table A with the data present in Table B.
Perfect, try this then :
Map_ID:
mapping
Load
Name,
ID
from B;
Map_No:
mapping
Load
Name,
No
from B;
A:
Load
Name,
if(isnull(ID), ApplyMap('Map_ID', Name), ID) as ID,
if(isnull(No), ApplyMap('Map_No', Name), No) as No
from A;
Hi,
It should work but does not in my case 😞
Here is my script.
I am joining all these tables to the Main Table and hence you will see Left Join in my Script.
Map_ID:
Mapping
Load Name, [Full ID];
SQL SELECT
FullNhm as Name,
FID as "Full ID"
FROM
"ID_Table;
Map_No:
Mapping LOAD
Name, [Full Number];
SQL SELECT
FullNhm as Name
FNO as "Full Number";
FROM
"No_Table;
Left Join(Main_Table)
Load
Name,
if(isnull([Full ID]), ApplyMap('Map_ID', [Full Name]), [Full ID]) as [Full ID],
if(isnull([Full No]), ApplyMap('Map_No', [Full Name]), [Full No]) as [Full No];
SQL SELECT *FROM "Org_Table";
I see empty values as original but not the applymap values filling the gaps. Is something wrong in my script and how SQL Combination is being used?
Try this exactly what i have done below & Please mark my answer as Correct if issue get resolved
************************************************************************
Tab1:
LOAD
Name,
ID ,
"No"
FROM [lib://Data/Fill Missing Values.xlsx]
(ooxml, embedded labels, table is [Tab A]);
Tab2:
LOAD
Name,
ID ,
"No"
FROM [lib://Data/Fill Missing Values.xlsx]
(ooxml, embedded labels, table is [Tab B])
where exists(Name) ;
NoConcatenate
FinTab:
Load Name,
ID,
No
Resident Tab1
where Len(Trim(ID));
Drop Table Tab1;
Hope it helps
Maybe isnt nulls, so :
if(isnull([Full ID]) or [Full ID] =''
And please don't do this, se ate generating the table, no need to join :
Left Join(Main_Table)
ApplyMap is the good choice here.