Sript prefix "Left Keep"

[One_day_I_will_know]

I have loaded two tables:

• one is a table, where I have already joined two tables from dw (in SQL) and then loaded it to with new name (Customers). It has about 1.500 rows.
• the other one is a table from dw with 70.000 rows from which I only need those 1.500 matching ones. I have loaded that one too with a new name (Sales). There is a common key that I have unqualified (ID) in both tables because I need it on other tables too.

Alas, the script below doesn’t work at all. I seem to have misunderstood something totally wrong when reading the qlik-help (https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixe...).

Of course I can do the left join in SQL, but in this case the “Keep” function sounded good because of the size difference on those tables. My logic tells me, that if I have already loaded the bigger table to Qlik Sense, I have already brought every unnecessary row too. But what is the right way to do this? My script doesn’t even recognize the names of the tables.

The script is below the “Customers” LOAD-script and the “Sales” table is in a separate section:

Select* from Customers;

Left keep select

ID

,Year

,Sales

,Sales_EU

,Sales_outside_EU;

[jonathandienst]

The Left Keep is similar to a  left join, except that the data is loaded into a separate table, rather than into the one table. A Left Keep will look at the field names that exist on both sides of the Join and load the records that match the values in the RHS of the Keep (Customers in your case). The snippet you posted appears not to have a FROM statement, so this will not work.

>>Alas, the script below doesn’t work at all.

Please be more specific - do you get an error, a cross join, no data at all???

>>My script doesn’t even recognize the names of the tables.

You need to resolve that first! 

[One_day_I_will_know]

Oh no... I did have the From statement but managed to edit it away.

The error code:

The following error occurred:
Connector reply error: SQL##f - SqlState: S0002, ErrorCode: 208, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Customers'.
The error occurred here:
Select* from Customers

And a bit more of my script:

Customers:
Load distinct
Name,
ID,
:
;

Select* from Customers;
Left keep select
ID
,Year
,Sales
,Sales_EU
,Sales_outside_EU;
,code
from Sales;

qualify*;
unqualify ID, code;

//and the rest is SQL script to join two tables

Customers:
select distinct

ID

,Code
:
;