Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load if value exists in another table

In SQL I would accomplish my objective using the IN operator in conjunction with another select statement but I'm not sure how to do this in Qlikview.

I have the following two tables.

WorkforceInfo:

LOAD

     EmployeeID

     Name

     PositionTitle

     PositionType

     OtherFields

FROM

     MyQVD

WHERE

     PositionType = 'Full Time'

;

Exceptions:

LOAD

     EmployeeID

FROM

     MySpreadsheet.xlsx

;

I need to load all records from my workforce info table where the position type is full time OR where the EmployeeID exists in my Exceptions spreadsheet. What is the best way to accomplish this?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Minor correction, I think the functions is called EXISTS

WHERE PositionType = 'Full Time' or Exists(EmployeeID);

edit:

... and take care that the field EmployeeID has not been loaded before (i.e. before the exceptions) in the script, Exists() is looking at all field values loaded so far.

To be on the safer side:

Exceptions:

LOAD

    EmployeeID as ExcludedEmpID

FROM MySpreadsheet.xlsx;

WorkforceInfo:

LOAD

    EmployeeID

    Name

    PositionTitle

    PositionType

    OtherFields

FROM

    MyQVD

WHERE PositionType = 'Full Time' or Exists(ExcludedEmpID,EmployeeID);


DROP TABLE Exceptions;

View solution in original post

14 Replies
sunny_talwar

There with Where Exist

Exceptions:

LOAD

    EmployeeID

FROM MySpreadsheet.xlsx;

WorkforceInfo:

LOAD

    EmployeeID

    Name

    PositionTitle

    PositionType

    OtherFields

FROM

    MyQVD

WHERE PositionType = 'Full Time' or Exists(EmployeeID);

UPDATE: Also bring the Exceptions table before the workforceinfo table so that Exist can check for EmployeeID in Exceptions table.

UPDATE2: Thanks Stefan for pointing the Exists. I have made the modification above

tamilarasu
Champion
Champion

Hi Chris,

Try like below,

Exceptions:

Load Concat(Chr(39) & EmployeeID & Chr(39) , ', ') as EmployeeID;

LOAD

     EmployeeID

FROM

     MySpreadsheet.xlsx

Let vEmp = Peek('EmployeeID');


Drop Table Exceptions;


WorkforceInfo:

LOAD

     EmployeeID

     Name

     PositionTitle

     PositionType

     OtherFields

FROM

     MyQVD

WHERE

     PositionType = 'Full Time' or Match(EmployeeID, $(vEmp));

sunny_talwar

Tamil -

Is there a reason to propose this over Exist function?

Best,

Sunny

swuehl
MVP
MVP

Minor correction, I think the functions is called EXISTS

WHERE PositionType = 'Full Time' or Exists(EmployeeID);

edit:

... and take care that the field EmployeeID has not been loaded before (i.e. before the exceptions) in the script, Exists() is looking at all field values loaded so far.

To be on the safer side:

Exceptions:

LOAD

    EmployeeID as ExcludedEmpID

FROM MySpreadsheet.xlsx;

WorkforceInfo:

LOAD

    EmployeeID

    Name

    PositionTitle

    PositionType

    OtherFields

FROM

    MyQVD

WHERE PositionType = 'Full Time' or Exists(ExcludedEmpID,EmployeeID);


DROP TABLE Exceptions;

sunny_talwar

Oops, my bad

This is what happens when I don't test before posting

Anonymous
Not applicable
Author

Well that was simple. Thanks! I was trying to accomplish this using the WildMatch function and wasn't having any luck.

tamilarasu
Champion
Champion

No Sunny. No specific reason. I have this piece of code in my system (Replied to someone earlier this evening). So just proposed the same.

sunny_talwar

Oh my god.... Anytime you respond to a post I have responded to, it is safe to assume that I am never going to get correct answer for it.

I think I need to stop being here. You have all got it covered

swuehl
MVP
MVP

Oh my god.... Anytime you respond to a post I have responded to, it is safe to assume that I am never going to get correct answer for it.

I think I need to stop being here. You have all got it covered

Hi Sunny, I believe that's just not true ... looking at your records