topic Re: Loading data exists in QlikView

Loading data exists

Wed, 07 Aug 2013 08:31:50 GMT

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

Re: Loading data exists

Wed, 07 Aug 2013 08:40:38 GMT

just use EXISTS() function.

Re: Loading data exists

tresB — Wed, 07 Aug 2013 08:43:37 GMT

try:

TrainingTable:

Load [Employee ID] From <?>;

Employee:

Load [Employee ID], otherfields

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

Re: Loading data exists

Wed, 07 Aug 2013 09:14:46 GMT

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

Re: Loading data exists

Wed, 07 Aug 2013 09:37:53 GMT

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

Re: Loading data exists

AbhijitBansode — Wed, 07 Aug 2013 10:31:56 GMT

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

Re: Loading data exists

Wed, 07 Aug 2013 11:10:13 GMT

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