topic Re: Using excel column in sql where clause in QlikView

Using excel column in sql where clause

emyemyemy — Sat, 16 Nov 2024 17:57:00 GMT

I want to use the quantity column from my excel table in the sql where clause !

this is the code that i tried but it didn't work :

LOAD product,Quantity
FROM
[MyFile]
(ooxml, embedded labels, table is Sheet) ;

right join

SQL
select product
from `DB`.`table` where
(Quantity - count(product)) <=0 group by product ;

Thanks for any help and attention !

Re: Using excel column in sql where clause

MayilVahanan — Tue, 20 Oct 2020 07:19:43 GMT

Hi @emyemyemy

One of the way to achieve based on ApplyMap concept like below.

MapQuantity:

Mapping Load product,Quantity
FROM
[MyFile]
(ooxml, embedded labels, table is Sheet) ;

Load *, where ApplyMap('MapQuantity', product, 0) - ProdCount <= 0;
select product, count(product) as ProdCount
from `DB`.`table` group by product ;

Hope it helps

Re: Using excel column in sql where clause

emyemyemy — Wed, 21 Oct 2020 09:14:29 GMT

You made my day , I have never woked with mapping , I will learn about it more !

Thank you for help !

Re: Using excel column in sql where clause

emyemyemy — Mon, 26 Oct 2020 08:59:05 GMT

Hi mayilvahanan !

Can you check my problem here I will be greatful !

https://community.qlik.com/t5/QlikView-App-Development/Get-Excel-column-list-that-it-s-value-doesn-t-exist-in-SQl/m-p/1755664#M453760