topic Re: Fill Missing Values in QlikView

Fill Missing Values

qlikwiz123 — Tue, 16 Feb 2021 20:16:25 GMT

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

Re: Fill Missing Values

QFabian — Tue, 16 Feb 2021 20:03:12 GMT

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

Re: Fill Missing Values

qlikwiz123 — Tue, 16 Feb 2021 20:17:37 GMT

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.

Re: Fill Missing Values

QFabian — Tue, 16 Feb 2021 20:23:28 GMT

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;

Re: Fill Missing Values

qlikwiz123 — Wed, 17 Feb 2021 05:08:52 GMT

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?

Re: Fill Missing Values

Arindam_Ghoshal_13 — Wed, 17 Feb 2021 06:20:40 GMT

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

Re: Fill Missing Values

QFabian — Wed, 17 Feb 2021 10:56:29 GMT

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)

Re: Fill Missing Values

Saravanan_Desingh — Wed, 17 Feb 2021 14:27:25 GMT

ApplyMap is the good choice here.