Where exists

[#1]

Hi All

I am having two tables

Table A: PRODUCT, CATEGORY

Table B: PRODUCT, CATEGORY, Sales

I want to choose only product category from Table B that exist in Table A

i am currently Using Where Exist (PRODUCT) and Exist (CATEGORY)

but not working properly

Please Suggest

[maxgro]

make a new field with product and category  (PRODUCT & '|' & 'CATEGORY as NewField)

use that field in where exists(NewField, PRODUCT & '|' & 'CATEGORY)

[#3]

HI Massimo

Thank You

But CATEGORY is a drill down group of PRODUCT

is it [ where exists(NewField, PRODUCT & '|' & 'CATEGORY)  ] possible

[#4]

Can you check the attachment

[#5]

Hi ,

You can just use:

WHERE Exists(PRODUCT,CATEGORY);

I think this could help.

Khushboo

[#6]

Hi Khushboo

I tried that way first in my data set and its not working as expected,

[#7]

Try this load script:

A:

LOAD PRODUCT,

     CATEGORY,

     Right(CATEGORY,3) as ProductKey

FROM

C:\QlikPrj\test\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

B:

LOAD PRODUCT,

     CATEGORY,

     SALES

FROM

C:\QlikPrj\test\Book1.xlsx

(ooxml, embedded labels, table is Sheet2)

WHERE Exists(ProductKey,PRODUCT);

DROP TABLE A;

[#8]

HI Gennaro

Thank you

But not the expected result

[#9]

Hi John,

Try this script that checks for the existence of the product and category.
I saw in the data categories in the two spreadsheets are reversed ie:
sheet1 = CATEGORY + PRODUCT
Sheet2 = PRODUCT + CATEGORY

A:

LOAD PRODUCT,

     CATEGORY,

     PRODUCT as ProductKey,

     Left(CATEGORY,Len(CATEGORY)-3) as CategoryKey

FROM

C:\QlikPrj\test\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

B:

LOAD PRODUCT,

     CATEGORY,

     SALES

FROM

C:\QlikPrj\test\Book1.xlsx

(ooxml, embedded labels, table is Sheet2)

WHERE Exists(ProductKey,PRODUCT) and Exists(CategoryKey,Right(CATEGORY,Len(CATEGORY)-LEN(PRODUCT)));

DROP TABLE A;

[qlikviewwizard]

Hi,

Try like this:

WHERE Exists(PRODUCT,CATEGORY);