Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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

Re: Checking one field in another table?

=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

Re: Checking one field in another table?

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

how to achieve that??

Highlighted
Specialist III
Specialist III

Re: Checking one field in another table?

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

Re: Checking one field in another table?

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

Highlighted
Specialist III
Specialist III

Re: Checking one field in another table?

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

Re: Checking one field in another table?

After that post the app.

Thanks

Highlighted
Not applicable

Re: Checking one field in another table?

you are asking me to remove synthetic keys?

Specialist III
Specialist III

Re: Checking one field in another table?

yes. after that post the app.

Highlighted
Not applicable

Re: Checking one field in another table?

PFA