Exclude Group in Set Analysis

[zekazak]

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

[kumarnatarajan]

Hi,

Try this one to your script.

LOAD

Distinct
Date,Order,ItenType

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

[Gysbert_Wassenaar]

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

[jagan]

Hi,

Please find attached file hope it helps you.

Regards,

Jagan.

[#5]

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

[zekazak]

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

[zekazak]

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

[Gysbert_Wassenaar · Accepted solution]

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

[zekazak]

Hi,

Awesome.It works. Thanks.

Best Regards,

~Sergejs