topic Re: maximum number of multiple values in set analysis in QlikView

maximum number of multiple values in set analysis

madushanfernand — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

Could some one please explain what is the maximum number of values we can pass into a dimension in set analysis?

and could explain what went wrong at following.?

I am using this expression to get all the list of values over a dimension called SalesKey.

=concat(distinct Aggr(max(SalesKey),Buyer,CPO),chr(39)&','&chr(39))

out put : a list of values

but when i use the same expression in set analysis it gives the output as 0.

=Sum ( {< SalesKey = {'$(=concat(distinct Aggr(max(SalesKey),Buyer,CPO),chr(160)&','&chr(160)))'}>} SALES)

Re: maximum number of multiple values in set analysis

anushree1 — Tue, 24 Sep 2019 06:31:39 GMT

I believe its due to the quotes encoding the ',' .

Also try to save the concatenated result in a variable and pass the variable in set analysis , that makes it easier to understand and use.

Also check for valid values on the Sales Key passed in set analysis

Re: maximum number of multiple values in set analysis

tresB — Tue, 24 Sep 2019 06:40:59 GMT

You are missing starting and ending quotes. Try like:

= chr(39) & concat(distinct Aggr(max(SalesKey),Buyer,CPO),chr(39)&','&chr(39)) &chr(39)

Re: maximum number of multiple values in set analysis

Arthur_Fong — Tue, 24 Sep 2019 07:00:32 GMT

Maybe try by using just max(SalesKey) without Aggr:

Sum ( {< SalesKey = {"=max(SalesKey)"}>} SALES)

Re: maximum number of multiple values in set analysis

madushanfernand — Tue, 24 Sep 2019 09:24:57 GMT

hi Tresesco

can you pls explain how this chr() works here?

Re: maximum number of multiple values in set analysis

tresB — Tue, 24 Sep 2019 09:41:55 GMT

It's not about chr() as a function. But about understanding your concat() anf it's separator you are using. Your are using chr(39)&','&chr(39) as separator which is essentially - ',' . That means concat would return something like:

value1','value2','value3...','valuen

however, in set analysis modifiers the comparison values expected to be withing single quotes. For ex, <Country={'USA', 'UK', 'FRANCE'}>. Now you look at the returned by your concat() has all quotes for values except the first and last one, and that is because your expression. To fix the missing quotes, try using my expression.

Re: maximum number of multiple values in set analysis

madushanfernand — Tue, 24 Sep 2019 10:19:04 GMT

Hi Tresesco

Thank you for making me understood on how chr() works and how it can be used in set analysis.
Anyway i managed to use the same expression i wrote initially to get the correct output. the only thing i did was i changed the length of the SalesKey only upto maximum 4 letters. then the same functions worked. i guess there is a limitation of character length what we can provide within quotes. correct me please if i am wrong.

Re: maximum number of multiple values in set analysis

tresB — Tue, 24 Sep 2019 10:48:57 GMT

I see your point. It might work without those quotes because your expression evaluation itself is enclosed by quotes.

There might be a limit in length (I am not sure though), but would not be anything less than hundred elements (if not more) I believe (I never tested though). How many values - in your case? Could you share a sample that shows the issue?

Re: maximum number of multiple values in set analysis

Brett_Bleess — Wed, 02 Oct 2019 18:54:05 GMT

Hey guys, adding a Design Blog post on Set Analysis, not sure if it may provide some further clarity or not:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

There are some other links in the post as well to other related posts. I check the Help as well, but I did not see anything related to limitations...

Regards,
Brett