topic Re: Checking one field in another table? in QlikView

Checking one field in another table?

Tue, 25 Aug 2015 17:33:38 GMT

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

Re: Checking one field in another table?

maxgro — Tue, 25 Aug 2015 18:54:37 GMT

=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

Re: Checking one field in another table?

Tue, 25 Aug 2015 18:58:15 GMT

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

how to achieve that??

Re: Checking one field in another table?

Qrishna — Tue, 25 Aug 2015 19:19:34 GMT

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.

Re: Checking one field in another table?

Tue, 25 Aug 2015 19:22:10 GMT

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

Re: Checking one field in another table?

Qrishna — Tue, 25 Aug 2015 19:25:06 GMT

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

Re: Checking one field in another table?

Qrishna — Tue, 25 Aug 2015 19:25:20 GMT

After that post the app.

Thanks

Re: Checking one field in another table?

Tue, 25 Aug 2015 19:26:24 GMT

you are asking me to remove synthetic keys?

Re: Checking one field in another table?

Qrishna — Tue, 25 Aug 2015 19:26:53 GMT

yes. after that post the app.

Re: Checking one field in another table?

Tue, 25 Aug 2015 19:28:37 GMT

PFA

Re: Checking one field in another table?

maxgro — Tue, 25 Aug 2015 19:29:36 GMT

maybe

=count ( {$<[Req Number]=P(

{

<Open.Status={Open},[Open.Opened Date]={"<=$(=floor(yearend(makedate(max(Year)))))"}>

<Filled.Status={Filled}>

}

[Req Number])>} DISTINCT [Req Number])

Re: Checking one field in another table?

Tue, 25 Aug 2015 19:38:57 GMT

It didnt worked like want we want want...

Re: Checking one field in another table?

Qrishna — Tue, 25 Aug 2015 19:42:29 GMT

Hy Nithin, you removed the syn key but u forgot to associate the tables.

complete the data modelling first.

Thanks