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

Fill empty values in table, based on another table

Fri, 02 May 2014 10:18:11 GMT

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:

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

Thanks for any help in this matter and regards,

Michelle

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

swuehl — Fri, 02 May 2014 10:25:09 GMT

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?

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

Fri, 02 May 2014 10:53:10 GMT

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:

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

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

Thanks you!!

Michelle

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

anbu1984 — Fri, 02 May 2014 10:54:52 GMT

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;

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

Mon, 05 May 2014 06:01:38 GMT

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