Load table based on values in another table

[rhyseretto]

This is probably dead easy, but I'm a little stuck.

I have two tables;

tblStore

RetailerID	StoreID
1	1
1	2
2	3
2	4

tblSales

StoreID	Sales
1	$5
2	$7
3	$3
4	$7

The second table is huge, and I want to drastically cut down on the number of records being loaded (currently the script is loading everything).

What I want is to load records from the Sales table where RetailerID = '1'.  I can do a match on RetailerID when loading tblStore, but tblSales is still loading everything. I imagine a join/keep is necessary, but as said, I can't figure it out.

Thanks!

[anthonyj · Accepted solution]

Hi,

It sounds like what you're looking for is the EXISTS( ) function.

If you only want to load in StoreID's that have been loaded from tblStore you load in the store table first with your "RetailerID = 1" condition then:

tblSales:

load

  StoreID,

  Sales

From Sales

where EXISTS(StoreID, StoreID);

You don't have to put the StoreID in their twice if the lookup column and load column is the same name but it's good practice. The first position is the name of the column that has already been loaded and the second is the column in the table you're loading.

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/InterRe...

Give this a try.

Regards

Anthony

[BtBread]

but it also works with the join.
I suspect that this is also more performant:

tblStore:
Load RetailerID, StoreID from.... where StoreID=1;

Left Join
tblSales:
Load StoreID, Sales from....;

[rhyseretto]

This is tremendous. Thanks so much, you're a lifesaver!