topic Re: Where exists in QlikView

Where exists

Thu, 10 Sep 2015 12:49:14 GMT

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

Re: Where exists

maxgro — Thu, 10 Sep 2015 12:52:19 GMT

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

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

Re: Where exists

Thu, 10 Sep 2015 12:56:29 GMT

HI Massimo

Thank You

But CATEGORY is a drill down group of PRODUCT

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

Re: Where exists

Thu, 10 Sep 2015 13:10:26 GMT

Can you check the attachment

Re: Where exists

Thu, 10 Sep 2015 13:25:25 GMT

Hi ,

You can just use:

WHERE Exists(PRODUCT,CATEGORY);

I think this could help.

Khushboo

Re: Where exists

Thu, 10 Sep 2015 13:53:14 GMT

Hi Khushboo

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

Re: Where exists

Thu, 10 Sep 2015 14:11:24 GMT

Try this load script:

LOAD PRODUCT,

CATEGORY,

Right(CATEGORY,3) as ProductKey

FROM

C:\QlikPrj\test\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD PRODUCT,

CATEGORY,

SALES

FROM

C:\QlikPrj\test\Book1.xlsx

(ooxml, embedded labels, table is Sheet2)

WHERE Exists(ProductKey,PRODUCT);

DROP TABLE A;

Re: Where exists

Thu, 10 Sep 2015 14:18:53 GMT

HI Gennaro

Thank you

But not the expected result

Re: Where exists

Thu, 10 Sep 2015 14:28:30 GMT

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

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

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;

Re: Where exists

qlikviewwizard — Thu, 10 Sep 2015 14:31:28 GMT

Hi,

Try like this:

WHERE Exists(PRODUCT,CATEGORY);

Re: Where exists

maxgro — Thu, 10 Sep 2015 17:58:46 GMT

see attachment