Load if value exists in another table

[Anonymous]

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?

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

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 · Accepted solution]

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]

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

[tamilarasu]

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]

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