Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

thomastc
New Contributor III

Mimic SQL's IN clause for resident table load.

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!

1 Solution

Accepted Solutions
pat_agen
Valued Contributor

Mimic SQL's IN clause for resident table load.

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

5 Replies
pat_agen
Valued Contributor

Mimic SQL's IN clause for resident table load.

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
New Contributor III

Mimic SQL's IN clause for resident table load.

Thanks I'll give that a go!

thomastc
New Contributor III

Mimic SQL's IN clause for resident table load.

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
Valued Contributor

Mimic SQL's IN clause for resident table load.

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
New Contributor III

Mimic SQL's IN clause for resident table load.

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!

Community Browser