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.

ckerr617
New Contributor II

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
MVP
MVP

Re: Load if value exists in another table

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;

13 Replies

Re: Load if value exists in another table

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

Re: Load if value exists in another table

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

Re: Load if value exists in another table

Tamil -

Is there a reason to propose this over Exist function?

Best,

Sunny

MVP
MVP

Re: Load if value exists in another table

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;

Re: Load if value exists in another table

Oops, my bad

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

ckerr617
New Contributor II

Re: Load if value exists in another table

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

Re: Load if value exists in another table

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.

Re: Load if value exists in another table

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

MVP
MVP

Re: Load if value exists in another table

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