Set analysis

[#1]

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

[Gysbert_Wassenaar]

See attached qvw.

[swuehl]

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.

[#4]

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!

[#5]

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

[qlikviewwizard]

Hi Maartje,

Please select Correct ,Helpful answers and close the thread.

Thank you.

[Gysbert_Wassenaar]

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) )

[#8]

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.

[swuehl]

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.

See also

The Magic of Variables

[#10]

Is there another option you suggest?