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

Loading data exists

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

6 Replies
Not applicable

Re: Loading data exists

just use EXISTS() function.

tresesco
Not applicable

Re: Loading data exists

try:

TrainingTable:

Load [Employee ID] From  <?>;

Employee:

Load [Employee ID], otherfields

From <> Where EXISTS ([Employee ID], [Employee ID]);

Not applicable

Re: Loading data exists

I tried it this way

http://pastebin.com/v9zjfMt4

this is not okay. See the relevant lines at ==>

The dumentation/hlp on exist is not clear to me.

John

Not applicable

Re: Loading data exists

Load the employee table first and left join on employee_id to the second table?

cabhijit
Not applicable

Re: Loading data exists

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];

Not applicable

Re: Loading data exists

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