topic Re: Where Exists not working in QlikView

Where Exists not working

stjernvd — Tue, 01 Mar 2016 17:36:13 GMT

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?

Re: Where Exists not working

Oleg_Troyansky — Tue, 01 Mar 2016 17:41:16 GMT

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!

Re: Where Exists not working

Anonymous — Tue, 01 Mar 2016 17:44:11 GMT

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

Re: Where Exists not working

maxgro — Tue, 01 Mar 2016 22:05:51 GMT

Main:

Load

Product,

Month,

Year,

Category

From

xxx;

Regions:

Load

Product,

[Net Sales],

[Gross Sales]

From

xxx

Where Exists (Product); // not Products

Re: Where Exists not working

Tue, 01 Mar 2016 23:55:05 GMT

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;

Re: Where Exists not working

jagan — Wed, 02 Mar 2016 01:27:05 GMT

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.

Re: Where Exists not working

engishfaque — Wed, 02 Mar 2016 04:02:21 GMT

Dear Stjernvd,

Try this one,

Syntax:

-----------

Where Exists (Table1_Field_Name, Table2_Field_Name);

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