do we have efficient load practice like subquery for large data?

[coloful_architect]

I have quite large size of data...in a form of QVD, it is already 2.3 G.

simplified data structure is like (let 's call it main table)

main table:

company

product 

measurements fields 

I wanna narrow down the  entire data into a smaller segment where only encompass company A's product portfolio.

in SQL , it is a sub query like

select * from main table where product =(select product from table A  where company='company A').

At qlik load, I tried resident load to get a flag field of product just for company A and then inner join with the main table.

but it seems, due to the size of file, as long as I run join, it goes forever.

if I just run without join, it goes through. 

do we have something like subquery at qlik load scirpt,? or any other alternative approach could solve my problem?

[Digvijay_Singh]

Not sure but what if you just create new  table as such - 

ProductA_table:

Reducedtable:

Load *

resident maintable

Where Company= 'CompanyA'

[martinpohl · Accepted solution]

how about:

load 

product

from your file

where company = 'companyA';

load * from your file where exists (product);

Regards

[SunilChauhan]

Table1:

Load distinct product from table A  where company='company A'

Left Join (Table1)

Load distinct * from main table;

Above left join is based on Product field.

Hope this helps

[coloful_architect]

my case ...is as long as I join , it goes forever...and seems to crash QMC...as well...

[SunilChauhan]

Have you checked for Common key field for example Product so there should be some id like Productid

or you should use

Autogenerate(Product) as Product in both the tables.

and also you should use only Required column from Big QVD. i.e 2.3 GB

May this helps

[coloful_architect]

thanks for your time...it turns out where exist works .....but still wrapping my head around why join does not work.

I have my table all set up as your suggestion says