topic Re: Exclude Group in Set Analysis in QlikView

Exclude Group in Set Analysis

zekazak — Wed, 17 Apr 2013 10:46:52 GMT

Hello,

How can I exclude all the rows of any particular order from the table if at least one of the rows of particular order has Item Type NOT "a".

Date	Order	ItemType
01/01/2013	1	a
01/01/2013	1	b
01/01/2013	1	c
01/01/2013	2	c
01/01/2013	2	d
01/01/2013	3	a
01/01/2013	4	a
01/01/2013	4	a

From the table above I expect to see the result like this:

Date	Order	ItemType
01/01/2013	3	a
01/01/2013	4	a

What would be a set analysis formula? Any help would be much appriciated.

Thanks in advance!

~Serg

Re: Exclude Group in Set Analysis

kumarnatarajan — Wed, 17 Apr 2013 11:15:04 GMT

Hi,

Try this one to your script.

LOAD

Distinct
Date,Order,ItenType

Resident TableName Where Order<>'1' and ItenType='a';

Re: Exclude Group in Set Analysis

Gysbert_Wassenaar — Wed, 17 Apr 2013 11:23:00 GMT

Try as expression MaxString({<Order=e({<ItemType-={'a'}>}Order)>} ItemType). See attached example.

Re: Exclude Group in Set Analysis

jagan — Wed, 17 Apr 2013 11:24:44 GMT

Hi,

Please find attached file hope it helps you.

Regards,

Jagan.

Re: Exclude Group in Set Analysis

Wed, 17 Apr 2013 13:06:24 GMT

There are many ways, this is the easiest for me

Add the field in as a dimension with an if statement

i.e. if(ItemType<>'a',ItemType,)

Then tick the tickbox for suppress nulls for this dimension.

All values that aren't 'a' will not appear on your report.

For set analysis you can use this MaxString({$<ItemType-={'a'}>}ItemType). The '-' before the equals sign will exclude all ItemType's that aren't a. However this - is not recognised function and so your expression will look like you have entered incorrect syntax but the expression will read ok

Cheers,

Byron

Re: Exclude Group in Set Analysis

zekazak — Wed, 17 Apr 2013 14:08:37 GMT

Hello all,

Thank you all for the very quick responces. It helped a lot. And I've got it done now.

Gysbert's answer is the one I've been looking for.

Best Regards,

~Sergejs

Re: Exclude Group in Set Analysis

zekazak — Thu, 18 Apr 2013 11:02:29 GMT

Hi Gysbert,

I have another quick question regarding to your set analysis expression.

The thing is that if I select Order 3 for example in your application it displays 4 rows instead of 1.

How should I amend the formula to tak into account the other selections? Thank you in advance.

~Sergejs

Re: Exclude Group in Set Analysis

Gysbert_Wassenaar — Thu, 18 Apr 2013 20:00:48 GMT

Try: MaxString({<Order=e({<ItemType-={'a'}>}Order)>*<Order={$(=concat(distinct Order,','))}>} ItemType)

Re: Exclude Group in Set Analysis

zekazak — Fri, 19 Apr 2013 15:23:08 GMT

Hi,

Awesome.It works. Thanks.

Best Regards,

~Sergejs