better way to build a selective data load

[coloful_architect]

I have a table, let say it is table A. see below 

table A

product 

company

measurement fields 

At company field, I have company A, company B, company C. I  want my data load to only have products from 

company A's portfolio. that means, all other products Company B and C do not produce, I dont want to load.

so what I am doing now is:

company A product flag table:

load product

         company

where company ='company A'

drop field company

inner join

table A 

for some reason this is going forever ....what is the problem? or any better alternative way?

[BrunPierre]

Your approach should be something like this

DATA:
LOAD * INLINE[
PRODUCT,COMPANY,CRATES
Beers,A, 200
Liquors,B
Non-alcoholics,C

];

INNER JOIN(DATA)
//---- company A product flag table
LOAD PRODUCT,
COMPANY,
'In Production' AS STATUS
RESIDENT DATA
WHERE MATCH(COMPANY,'A');

DROP Field COMPANY;

EXIT SCRIPT;

[coloful_architect]

I actually tried resident table and pretty much like your way except I used where company= 'company A' rather than your match... it did not work...

may I ask what is that 'as status' for?

...and interesting, as long as I just load two tables without any join statement. it goes through.....but as long as I tries to join, it seems to go infinite  loop

[BrunPierre]

Our data set is dissimilar. Nonetheless, the match can be used in your case as "match(company,'company A')"

And please disregard the status that wasn't needed in your case.

Could it be that the tables are linked in such a way that there are several connection paths between two fields?

Usually, when such loops occur, you might want to rename the identical field names. Otherwise, share your actual script.

[coloful_architect]

Hi Peter,

now I am thinking if the seemingly infinite loop of loading is due to the size of my data...my actual main table is 2.3 G. 

I created a separate qvd  file particular for the flag of company and as long as it operates join statement, it just goes forever...

do you by chance know if Qlik has subquery function as SQL ? 

[coloful_architect · Accepted solution]

ok...I finally solved my problem by using where exists 

I load two tables without join statement.  one only has product flag and the other has everything.

sth like this 

table for flag:

load product as flag

main table

load *

where exists (flag, product)