topic Mimic SQL's IN clause for resident table load. in QlikView

Mimic SQL's IN clause for resident table load.

thomastc — Tue, 28 Jun 2011 10:45:35 GMT

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!

Mimic SQL's IN clause for resident table load.

pat_agen — Tue, 28 Jun 2011 10:53:35 GMT

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;

Mimic SQL's IN clause for resident table load.

thomastc — Tue, 28 Jun 2011 10:55:49 GMT

Thanks I'll give that a go!

Mimic SQL's IN clause for resident table load.

thomastc — Tue, 28 Jun 2011 12:55:03 GMT

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.

Mimic SQL's IN clause for resident table load.

pat_agen — Tue, 28 Jun 2011 13:00:20 GMT

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

Mimic SQL's IN clause for resident table load.

thomastc — Wed, 29 Jun 2011 08:22:09 GMT

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!