topic Set analysis in QlikView

Set analysis

Thu, 27 Aug 2015 16:34:21 GMT

Hi,

I have a question about set analysis.I started to make a table with the dimention User1 and sum(Count). This works . Now I would like make a second column of sum(Count) of User2. Anybody a suggestion? Your help is very much appreciated!

Kind regards

Re: Set analysis

Gysbert_Wassenaar — Thu, 27 Aug 2015 17:10:00 GMT

See attached qvw.

Re: Set analysis

swuehl — Thu, 27 Aug 2015 17:14:01 GMT

Instead of having two columns User1 and User2, create a single User column:

USER:

LOAD User1 as User,

1 as Source

RESIDENT YourTable;

LOAD User2 as User,

2 as Source

RESIDENT YourTable;

Then create a chart with dimension User and two expressions:

=Count({<Source = {1}>} User)

=Count({<Source = {2}>} User)

For the above field transformation, you can also use the CROSSTABLE LOAD prefix.

Re: Set analysis

Thu, 27 Aug 2015 20:34:11 GMT

Hi Gysbert,

pick(match(User1,'Vincent','Dan','David'),

count({<User2={'Vincent'}>} total Count),

count({<User2={'Dan'}>} total Count),

sum({<User2={'David'}>} Count)

)

Thanx, that works perfect without changing the architecture of my data. However, it's a lot of hard coded names. Do you have a suggestion how to make this thing variable? I have more than 40 names. Thanks in advance!

Re: Set analysis

Thu, 27 Aug 2015 20:41:32 GMT

Thanks, that works perfect. I rather not change my data architecture. However, the expressions are nice and simple like this. Thanks for your time!

Re: Set analysis

qlikviewwizard — Fri, 28 Aug 2015 05:31:28 GMT

Hi Maartje,

Please select Correct ,Helpful answers and close the thread.

Thank you.

Re: Set analysis

Gysbert_Wassenaar — Fri, 28 Aug 2015 05:32:45 GMT

You can generate the pick match too using variables:

vUserList: =concat(distinct chr(39) & User1 & chr(39) ,',')

vExpList: =concat(DISTINCT 'count({<User2={' & chr(39) & User1 & chr(39) & '}>}total Count)' ,',', User1)

pick(match(User1, $(vUserList)), $(vExpList) )

Re: Set analysis

Fri, 28 Aug 2015 08:11:46 GMT

It does not work yet. Do you see what I do wrong? When I look at your variables they look good. The only thing I could think of was that one bracket ')'was forgotten between $(vUserList) and the comma to close the match function. However, my column is still empty. But the function looks the same as the hard coded one, so i'm confused.

Re: Set analysis

swuehl — Fri, 28 Aug 2015 08:52:05 GMT

Could be an issue due to the way you define and expand your variables (i.e. defining with or without leading equal sign), so that the expanded UserList will contain the concat expression, not the strings, comma separated.

Re: Set analysis

Fri, 28 Aug 2015 11:32:59 GMT

Is there another option you suggest?

Re: Set analysis

Fri, 28 Aug 2015 11:44:43 GMT

I will once I know what the best answer is.

Re: Set analysis

Gysbert_Wassenaar — Fri, 28 Aug 2015 11:55:13 GMT

See attached qvw

Re: Set analysis

Digvijay_Singh — Fri, 28 Aug 2015 13:04:39 GMT

With valuelist and full of variables