topic Re: Get Excel column list that it's value doesn't exist in SQl column in QlikView

Get Excel column list that it's value doesn't exist in SQl column

emyemyemy — Sat, 16 Nov 2024 17:55:33 GMT

I want to select the products from my excel column 'ExcelProduct' that are not exist in my sql column SqlProduct:

I tried this :

sql
select product as sqlproduct
from `DB`.`Table` group by product;

let vProductlist= peek('sqlproduct');

LOAD product as excelProduct,date,CC
[D:\\All Documents\file.xlsx]
(ooxml, embedded labels, table is sheet)

where date=today(1) AND CC='360' and not Exists(product,$(vProductlist));

I tried to use not in ($(vProductlist)) but it's only works with sql query !

Thank you for any help or attention!

Re: Get Excel column list that it's value doesn't exist in SQl column

rubenmarin — Sat, 24 Oct 2020 17:04:15 GMT

Hi @emyemyemy, try with: and not Exists('sqlproduct', product);

Re: Get Excel column list that it's value doesn't exist in SQl column

emyemyemy — Mon, 26 Oct 2020 08:47:38 GMT

Hi Ruben !

It gives me all count productExcel ignoring the condition 'CC='360' :

I have 5 productExcel where CC=360 and 2 productExcel=361 the result shows 7 for count(productExcel) ignoring the where CC=360 while it takes today's date !

,I can't figure out !

I want to limit my result on specific productExcel that are not exist in my productsql list where CC='360'!

Thank you for your help !

Re: Get Excel column list that it's value doesn't exist in SQl column

marcus_sommer — Mon, 26 Oct 2020 09:21:50 GMT

Try it in this way:

sql select product from `DB`.`Table` group by product;

LOAD product,date,CC from [D:\\All Documents\file.xlsx]
(ooxml, embedded labels, table is sheet)
where date=today(1) AND CC='360' and not Exists(product);

- Marcus

Re: Get Excel column list that it's value doesn't exist in SQl column

MayilVahanan — Mon, 26 Oct 2020 09:23:12 GMT

Hi @emyemyemy

Try like below

Sql
select product as sqlproduct
from `DB`.`Table` group by product;

LOAD product as excelProduct,date,CC
[D:\\All Documents\file.xlsx]
(ooxml, embedded labels, table is sheet)

where date=today(1) AND CC='360' and not Exists(sqlproduct,product );

Re: Get Excel column list that it's value doesn't exist in SQl column

emyemyemy — Mon, 26 Oct 2020 12:29:32 GMT

Thank you for help , It misses only the ( '' ) : and not Exists('sqlproduct',product );

Re: Get Excel column list that it's value doesn't exist in SQl column

emyemyemy — Mon, 26 Oct 2020 13:20:35 GMT

Hi Ruben !

I had problem with my DB , It works perfectly thank you for help .