Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Checking one field in another table?

Hi All,

I have two tables namely Open and Filled in which i have Open Positions and Filled Positions data respectively.

Now i need to show, Number open Positions(Req Number) from min(OpenDate) to selected year and it should check for it is Filled or not in the same time span.

I used the following exp

=count ({<Open.Status={'Open'},[Open.Opened Date]={"<=$(Year) "},Filled.Status={"<>$('Filled')"}>}DISTINCT [Req Number])

Please look at Attached app

the correct answer is if you select 2013 it should show 1 and if you select 2014 it should show 29 if you select 2015 or nothing it should show 87

12 Replies
Highlighted
MVP
MVP

=count ({<Open.Status={'Open'},[Open.Opened Date]={"<=$(=Floor(YearEnd(MakeDate(Max(Year)))))"}>}DISTINCT [Req Number])

I remove the Filled.Status <> Filled (Filled.Status=-{Filled}) because all records are Filled

Highlighted
Not applicable

Yes but i need the Req numbers from Open Table which are not present in Filled Table.

how to achieve that??

Specialist III
Specialist III

Hi Nithin,

try solving the synthetic keys first in your app.

For Req numbers from Open Table which are not present in Filled Table :

Use Where not exists() condition.

I cannot reload the app here.

Highlighted
Not applicable

i want it expression level. because it should respond to the filter

Highlighted
Specialist III
Specialist III

Thats what. try resolving the synthetic key first. if you have syn keys theres a possibilty that your count may not be accurate.

Highlighted
Specialist III
Specialist III

After that post the app.

Thanks

Highlighted
Not applicable

you are asking me to remove synthetic keys?

Highlighted
Specialist III
Specialist III

yes. after that post the app.

Highlighted
Not applicable

PFA