Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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?
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
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;
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