Complex set analysis

[rachel_delany]

I am struggling to get this set analysis to work correctly.

Example of what I would like.

Count Id where:

Type='16' and X="*MB*"

or

Type='16' and Y="*"

I tried using COUNT({<{Type={'16'}, X={"*MB*"}>+<{Type={'16'}, Y={"*"}>} Id)

But it's not producing the correct result.

Any ideas?

[justISO]

Hi, first, question, what you want to find using "*MB*" and "*"? Take values if it is exact '*MB*' or you looking 'MB' somewhere in the middle of string, f.e. 'GGGMBTT' would also be included? If so, than this should work:

count({<Type={'16'}, X={"*MB*"}> + <Type={'16'}, Y={"*"}>} ID)

If not, change double quotes " to single one ' to take f.e. values where it is only symbol '*'.

[rachel_delany]

Hi,

Yes I just want to find where it contains MB as part of the string.

That expression was what I had already tried and isn't giving the correct result.

[justISO]

Ok, but with your logic you now just counting every ID with type=16, as  Y={"*"} taking everything, even null values, and your OR is not needed. Also, you are using '+' in set analysis, which I believe means AND or union. For OR '/' should be used.

[rachel_delany]

Didn't we both write the same expression?

Y={"*"} will exclude nulls

+ is an OR, * is and

[Lauri]

Your original expression looks correct. What does your data model look like? Are all fields in one table? Are there possibly duplicate rows in a table (as a result of a join in your load script), inflating the count?

[agigliotti]

Hi @rachel_delany ,

Maybe it's only a syntax error with brackets, see below:

COUNT( {< Type = {'16'}, X = {"*MB*"} > + < Type = {'16'}, Y = {"*"} >} Id )

Best Regards

[Saurabh_K14999]

Hi,

Try this expression -

COUNT(DISTINCT {<Type={'16'}, X={"MB"}>+<Type={'16'}, Y-={''}>} Id)

Regards,

SK