Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables one with training records having a field called employee ID. Another table holds the employee data with employeeID employee name and organization code.
I have 55000 records where there is no employee found in the employee table.
This data is loaded completely now creating unnecessary large files. How can I load only those training records where there is a hit in the employee table?
Something in the from clause?
Thanks
John
just use EXISTS() function.
try:
TrainingTable:
Load [Employee ID] From <?>;
Employee:
Load [Employee ID], otherfields
From <> Where EXISTS ([Employee ID], [Employee ID]);
I tried it this way
this is not okay. See the relevant lines at ==>
The dumentation/hlp on exist is not clear to me.
John
Load the employee table first and left join on employee_id to the second table?
Two options are available :
//------------using exists ----------------
[Emp]:
Load
EmpId,
EmpName
OrgCode
From [Emp];
[Training]:
Load
EmpId,
// other columns
From [Trainings]
Where Exists(EmpId,EmpId);
//--------using keep----------------
[Emp]:
Load
EmpId,
EmpName
OrgCode
From [Emp];
[Training]:
Left Keep ([Emp])
Load
EmpId,
// other columns
From [Trainings];
I tried both your suggestions.
FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([LocalEmpNumber]) and [Registration Status] = 'Completed' | |
and exists (EMPLOYEE_ID,EMPLOYEE_ID) |
It gave me a training table without records in both cases.
I also tried Employees.EmployeeID = Training.EmployeeID but also unsuccesfull.
John