Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
rhyseretto
Contributor III
Contributor III

Load table based on values in another table

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

I have two tables;

tblStore

RetailerIDStoreID
11
12
23
24

 

tblSales

StoreIDSales
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!

1 Solution

Accepted Solutions
anthonyj
Creator III
Creator III

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

View solution in original post

3 Replies
anthonyj
Creator III
Creator III

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

View solution in original post

BtBread
Creator
Creator

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

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