Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stjernvd
Partner - Creator
Partner - Creator

Where Exists not working

Hi,

I have two tables, my main one and then a second table.

Main:

Product,

Month,

Year,

Category

From

xxx;

Regions:

Product,

Net Sales,

Gross Sales

From

xxx

Where Exists (Products);

What I want to do is load the main and second table and link them via 'Product', and I want to load the second table for only those 'Product' values that already exist in the Main table, any that don't match should not be loaded.

Why isn't this working?

6 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

The script you listed here, isn't your real script (the keywords LOAD are missing etc...) In this script, your field name is Product, but you are using the name Products (in plural) in your where Exists() - that could be a problem, if the same error exists in your script.

Another reason could be if you are trying to use where exists() in your SQL SELECT statement. Exists() is a QlikView function, and it can only be used in a LOAD statement, not in SQL SELECT.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

Anonymous
Not applicable

Hi, Try it,

Main:

Product,

Month,

Year,

Category

From

xxx;

Regions:

Product,

Net Sales,

Gross Sales

From

xxx

;

RIGHT JOIN

load

Product

Resident  Main;

Regards

maxgro
MVP
MVP

Main:

Load

     Product,

     Month,

     Year,

     Category

From

     xxx;

Regions:

Load

     Product,

     [Net Sales],

     [Gross Sales]

From

     xxx

Where Exists (Product);     // not Products

Not applicable

The Easiest way is to left join your Regions Table to your main table.. this will only load Values from Regions table that will match with Product in Main table

The below script will only load one table called 'Main' containing all the data from Main Table and Matching values from Second table.

Main:

Load     Product,

            Month,

          Year,

          Category

From ABC;

Left Join

  Load           Product,

                    Net Sales,

                    Gross Sales

From XYZ;

jagan
Luminary Alumni
Luminary Alumni

Hi,

Are you using SQL queries or Load statments? If you are using SQL Statement then Exists won't work, you can use LEFT KEEP as an alternative

Main:

SELECT

Product,

Month,

Year,

Category

From TableName1;

Regions:

LEFT KEEP(Main)

SELECT

Product,

Net Sales,

Gross Sales

From TableName2;

If you are using Load statement then the issue is at

Where Exists (Products);  change it to


Where Exists (Product);


Hope this helps you.


Regards,

Jagan.

engishfaque
Specialist III
Specialist III

Dear Stjernvd,

Try this one,


Syntax:

-----------

Where Exists (Table1_Field_Name, Table2_Field_Name);

Or

If you have same field name in both tables, then use following syntax

Syntax:

-----------

Where Exists (Field_Name_That_is_Common_in_Both_Tables);

Kind regards,

Ishfaque Ahmed