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: 
Not applicable

Fill empty values in table, based on another table

Hello all,

I have a table, in which certain values are missing. In this case, the Oracle Employee Numbers are missing.

What I would like to do is fill these empty Oracle Employee ID values in my base table (which contains SAP Project and SAP_Employee). The Oracle Employee numbers are in another table and the link between the two tables is the field SAP employee.

I've tried left/right/outer/inner joins etc - but feel like I'm missing something.

More visually:

file1.png

The erroneous script I currently have is:

BaseTable:
LOAD Oracle_Project,
SAP_Project,
Oracle_Employee,
SAP_Employee
FROM
[Base table.xlsx]
(
ooxml, embedded labels, table is Sheet1);

outer Join (BaseTable)

LOAD DISTINCT
SAP_Project,
SAP_Employee
FROM
[Naomi table.xlsx]
(
ooxml, embedded labels);



/*join (BaseTable)
Load
SAP_Employee,
Oracle_Employee
FROM
[EMployees.xlsx]
(ooxml, embedded labels, table is Sheet1);

But that leads to the following output, which is not what I'm looking for

On the screenshot, you see that the first 4 rows did not receive an Oracle_Employe ID..

file2.png

Thanks for any help in this matter and regards,

Michelle

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

Try this


BaseTable:
LOAD Oracle_Project,
SAP_Project,
Oracle_Employee,
SAP_Employee
FROM
[Base table.xlsx]
(
ooxml, embedded labels, table is Sheet1);

Left Join (BaseTable)

LOAD DISTINCT
SAP_Employee,

Oracle_Employee As Ora_Emp

FROM
[Naomi table.xlsx]
(
ooxml, embedded labels);


Final:

NoConcatenate

Load Oracle_Project,
SAP_Project,
If(IsNull(Oracle_Employee) ,Ora_Emp,Oracle_Employee),
SAP_Employee Resident BaseTable;

Drop table BaseTable;

View solution in original post

4 Replies
swuehl
MVP
MVP

Michelle, I can't see the second table you want to load the missing base table information for Oracle_Employee from.

Naomi table does not contain that information, right?

Not applicable
Author

Hi!

Thank you for the quick reply!

The second table is relatively easy: it contains values for Oracle_Employee, and the link to the first table are some values for SAP Employee:

file3.png

So that the table in QV would look like this (yellow cells)

file4.png

(BTW this is a simplified example - just need to understand the concept )

Thanks you!!

Michelle

anbu1984
Master III
Master III

Try this


BaseTable:
LOAD Oracle_Project,
SAP_Project,
Oracle_Employee,
SAP_Employee
FROM
[Base table.xlsx]
(
ooxml, embedded labels, table is Sheet1);

Left Join (BaseTable)

LOAD DISTINCT
SAP_Employee,

Oracle_Employee As Ora_Emp

FROM
[Naomi table.xlsx]
(
ooxml, embedded labels);


Final:

NoConcatenate

Load Oracle_Project,
SAP_Project,
If(IsNull(Oracle_Employee) ,Ora_Emp,Oracle_Employee),
SAP_Employee Resident BaseTable;

Drop table BaseTable;

Not applicable
Author

That works, thanks so much!!

I did learn that join + load distinct can sometimes be a bit tricky

See: http://www.qlikfix.com/2013/07/30/distinct-can-be-deceiving/

BR,

Michelle