Exists condition not giving correct results

[#1]

Hi All,

I have a requirement where my query has hardcoaded values in where clause

SELECT * FROM PROD."PRODUCTS_DIM" where eff_to is null and product_family_3 = 'FLIP'
AND PRODUCT_TYPE_3 IN
('FLIP30',
'FLIP60',
'FLIPTV',
'MINO60',
'MINOHD120',
'MINOHD60',
'PROMO',
'REFURB',
'SLIDE',
'ULTRA120',
'ULTRA30',
'ULTRA60',
'ULTRAHD120',
'ULTRAHD60');

I have to pick up PRODUCT_TYPE_3 values from Excel sheet instead of hard coading it into the script so that user can add or delete the values .

To achieve this I have created a script with Exists like following

Test2:
LOAD

Product as PRODUCT_TYPE_3
FROM (ooxml, embedded labels, table is Sheet1);

Product:
Load * ;

SQL Select * FROM PROD."PRODUCTS_DIM" where PRODUCT_FAMILY_3 = 'FLIP'
AND EFF_TO IS NULL;

STORE Product INTO [$(dashdataDir)\ENGINEERING\Product.qvd];

Drop table Product;

Product1:
Load *
FROM [$(dashdataDir)\ENGINEERING\Product.qvd] (qvd) where EXISTS(PRODUCT_TYPE_3,PRODUCT_TYPE_3);

But I believe there is something wrong in my script or Exists clause as my counts are not matching.

Here is my excel data

Product

FLIP30

FLIP60

FLIPTV

MINO60

MINOHD120

MINOHD60

PROMO

REFURB

SLIDE

ULTRA120

ULTRA30

ULTRA60

ULTRAHD120

ULTRAHD60

Please guide me how I can achieve this through qlikview.

Thanks,

Rashmi

[pover]

You shouldn't have an exists function with fields of the same name like exists(field1,field1). You need change that to exists(field1,field2).

For example, in your script first make an additional column with

Product as PRODUCT_TYPE_3,
Product as PRODUCT_EXISTS

and then change the exists function to

Product1:
Load *
FROM [$(dashdataDir)\ENGINEERING\Product.qvd] (qvd) where EXISTS(PRODUCT_EXISTS,PRODUCT_TYPE_3);

Regards.