topic Re: Qlikview equivalent for SQL "IN" function in QlikView

Qlikview equivalent for SQL "IN" function

suryaa30 — Thu, 07 Sep 2017 18:01:06 GMT

Hi All,

assuming I have 200 products and based on some calculation I have narrowed down certain products assigned to a variable using concat function and whose output can vary in number.

I have a calculated variable that generates the output as ('Shoe','Pen','Pencil'..........)

vWantedProduct=CONCAT({SET Expression} Product)

My question is assuming I have a straight table and with Dimension as product and some expressions.

What needs to be done to limit the dimensions in straight table only to those values corresponding to the values in vWantedProduct.

if the number of values in vWantedProduct was few and static I could have used IF else condition, but now it is dynamic.

How do I proceed?

for example:Assuming we had "IN" function in qlikview just for understanding. below should be my calculated dimension.

IF( Product is IN(vWantedProduct) then Product)

Re: Qlikview equivalent for SQL "IN" function

sunny_talwar — Thu, 07 Sep 2017 18:04:21 GMT

May be use set analysis

Sum({<Product = {$(=Concat({SET Expression} Product))}>} Measure)

I believe that you already have a way to get single quotes around the list of products, so use the dollar sign expansion without any single or double quotes

Re: Qlikview equivalent for SQL "IN" function

sunny_talwar — Thu, 07 Sep 2017 18:05:34 GMT

Alternatively, you can try this

If(Match(Concat(TOTAL {SET Expression} Product), Product), Product)

Re: Qlikview equivalent for SQL "IN" function

suryaa30 — Thu, 07 Sep 2017 18:48:24 GMT

Let me just check this. Thanks Much