topic Re: Complex set analysis in App Development

Complex set analysis

rachel_delany — Mon, 12 Dec 2022 01:47:45 GMT

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*"

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?

Re: Complex set analysis

justISO — Mon, 12 Dec 2022 07:42:01 GMT

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 '*'.

Re: Complex set analysis

rachel_delany — Mon, 12 Dec 2022 22:02:55 GMT

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.

Re: Complex set analysis

justISO — Tue, 13 Dec 2022 07:20:07 GMT

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.

Re: Complex set analysis

rachel_delany — Tue, 13 Dec 2022 21:58:31 GMT

Didn't we both write the same expression?

Y={"*"} will exclude nulls

+ is an OR, * is and

Re: Complex set analysis

Lauri — Thu, 15 Dec 2022 14:40:24 GMT

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?

Re: Complex set analysis

agigliotti — Thu, 15 Dec 2022 14:56:33 GMT

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

Re: Complex set analysis

Saurabh_K14999 — Tue, 20 Dec 2022 11:37:09 GMT

Hi,

Try this expression -

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

Regards,