Mimic SQL's IN clause for resident table load.

[thomastc]

Hello,

Is it possible to mimic a sub query in an SQL IN clause when loading from a qvd? I have a master list in a qvd file but only want to load data that is already present in my resident tables.

For instance I have Tab1 and Tab2 both with the field ‘GP’ for instance:

     Tab1.sGP

     Tab2.pGP

I have a master list of GPs in a qvd file but I only want to load GPs that are in both Tab1 and Tab2.

Please note that Tab1 and Tab2 are resident tables.

I am looking for the equivalent of:

masterPrac:
NoConcatenate
LOAD GP AS bridgeGP 
From D:\Qlikview\QV Data\tbl_GP.qvd (qvd)
Where GP IN(SELECT sGP Resident Tab1) 
AND   GP IN(Select pGP Resident Tab2);

Many thanks!

[pat_agen]

hi,

the code should look somehing like this (untested)

bridgeGP:

load

     sGP as bridgeGP

resident Tab1;

bridgeGP:

load

     pGP as bridgeGP

resident Tab2;

masterPrac:

load GP as bridgeGP

from your qvd file here

where exists(bridgeGP,GP);

drop table bridgeGP;

[thomastc]

Thanks I'll give that a go!

[thomastc]

Thank you again for the reply.

That essentially is what I have at the moment. I need something that will give me only the values that are in both Tab1 and Tab2.

For example if Tab1 has the value ‘A76’ but Tab2 does not then the masterPrac table should not contain ‘A76’.

Also if Tab2 has the value 'G29' and 'G29' is not in Tab1 then masterPrac should not contain 'G29'.

Thanks.

[pat_agen · Accepted solution]

hi,

my oversight

should be solved by slightly chnaging the creation of your master list as follows:

bridgeGP:

load

     sGP as bridgeGP

resident Tab1;

bridgeGP:

inner join (bridgeGP) load

     pGP as bridgeGP

resident Tab2;

then rest of code as above

[thomastc]

That worked, thank you!

Still getting used to the QV load scripts.

As an addition I used the DISTINCT qualifier, which seemed to reduce the loading time somewhat.

bridgeGP:

load DISTINCT

     sGP as bridgeGP

resident Tab1;

Many thanks again!