Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Where Exists not working

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!

Highlighted
Specialist
Specialist

Re: Where Exists not working

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

Highlighted
MVP
MVP

Re: Where Exists not working

Main:

Load

     Product,

     Month,

     Year,

     Category

From

     xxx;

Regions:

Load

     Product,

     [Net Sales],

     [Gross Sales]

From

     xxx

Where Exists (Product);     // not Products

Highlighted
Not applicable

Re: Where Exists not working

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;

Highlighted
MVP & Luminary
MVP & Luminary

Re: Where Exists not working

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.

Highlighted
Specialist III
Specialist III

Re: Where Exists not working

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