Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
One_day_I_will_know
Contributor II
Contributor II

Sript prefix "Left Keep"

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;

Labels (1)
2 Replies
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! 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
One_day_I_will_know
Contributor II
Contributor II

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
:
;