topic Load if value exists in another table in QlikView

Load if value exists in another table

Anonymous — Fri, 20 May 2016 15:42:46 GMT

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?

Re: Load if value exists in another table

sunny_talwar — Fri, 20 May 2016 15:49:01 GMT

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

tamilarasu — Fri, 20 May 2016 15:50:52 GMT

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

sunny_talwar — Fri, 20 May 2016 15:53:08 GMT

Tamil -

Is there a reason to propose this over Exist function?

Best,

Sunny

Re: Load if value exists in another table

swuehl — Fri, 20 May 2016 15:55:30 GMT

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

sunny_talwar — Fri, 20 May 2016 15:56:44 GMT

Oops, my bad

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

Re: Load if value exists in another table

Anonymous — Fri, 20 May 2016 15:59:55 GMT

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

tamilarasu — Fri, 20 May 2016 16:01:40 GMT

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

sunny_talwar — Fri, 20 May 2016 16:03:30 GMT

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

Re: Load if value exists in another table

swuehl — Fri, 20 May 2016 16:08:39 GMT

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

Re: Load if value exists in another table

sunny_talwar — Fri, 20 May 2016 16:11:38 GMT

All those points are from when you are inactive

I think we should divide and conquer. When you are not here I can cover for you (partially) and then when you are here. I will go back to my other business

Re: Load if value exists in another table

Kushal_Chawda — Fri, 20 May 2016 16:12:15 GMT

sunny macha raha tu

Re: Load if value exists in another table

sunny_talwar — Fri, 20 May 2016 16:13:23 GMT

No problem at all, our trio of Stefan, Tamil and Sunny is glad to help you anytime

Best,

Sunny

Re: Load if value exists in another table

Anonymous — Fri, 20 May 2016 16:20:40 GMT

You guys are awesome. I was messing around with this for an hour before I posted. Thanks for saving my afternoon!

Re: Load if value exists in another table

fbar — Sat, 09 Jul 2022 10:18:25 GMT

Tamilarasus solution is WAY faster; I often use it for big data sets in QVD (>10 Mio Entries)