topic Re: do we have efficient load practice like subquery for large data? in Connectivity & Data Prep

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

coloful_architect — Mon, 22 Aug 2022 14:41:42 GMT

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?

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

Digvijay_Singh — Mon, 22 Aug 2022 14:54:32 GMT

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

ProductA_table:

Reducedtable:

Load *

resident maintable

Where Company= 'CompanyA'

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

martinpohl — Mon, 22 Aug 2022 14:56:51 GMT

how about:

load

product

from your file

where company = 'companyA';

load * from your file where exists (product);

Regards

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

SunilChauhan — Mon, 22 Aug 2022 15:07:39 GMT

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

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

coloful_architect — Mon, 22 Aug 2022 15:21:50 GMT

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

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

SunilChauhan — Mon, 22 Aug 2022 15:34:49 GMT

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

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

coloful_architect — Mon, 22 Aug 2022 16:40:48 GMT

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