Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Honored Contributor III

Re: Fill empty values in table, based on another table

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;

4 Replies
MVP
MVP

Re: Fill empty values in table, based on another table

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

Re: Fill empty values in table, based on another table

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
Honored Contributor III

Re: Fill empty values in table, based on another table

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

Re: Fill empty values in table, based on another table

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