help in set analysis expression (count if exists)

[ioannagr]

Hi experts,

I have a table with fields ID, tests, results. The tests at most are 5.

Each ID can have 0,1,2,3,4 or 5(thus all) tests.

All tests may have results:  (null), positive, negative

For example ID 1 can have Test1, Test2 but not Test3 to Test 5.

All tests are not implemented on all IDs.

What I do now is :

count({(<[result test 1]={'negative'}>)*
(<[result test 2]={'negative'}>)*
(<[result test 3]={'negative'}>)*
(<[result test 4]={'negative'}>)*
(<[result test 5]={'negative'}>)
}
distinct [ID])

but gives me less than expected because not all tests are done to every ID, so the result is 0 for many.

I want a set analysis that returns for every ID : IF any of the tests exist for each ID, then if they are negative, then count this ID.

Help me out please! 

[JustinDallas]

Can you supply us with some dummy data?

[ioannagr]

Hi @JustinDallas ,not unfortunately but the table looks like this

ID  TEST    RESULT

1        1            neg

1        2           neg

2        2           pos

2        3           neg

3        1           pos

3         2          neg

3        3           neg

3       4             neg

3      5              neg

4       3             neg

4       4             neg

So my expected count for distinct IDs now would be 2 (ids 1 and 4)

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT {<ID = {"=RangeMaxString([result test 1], [result test 2], [result test 3], [result test 4], [result test 5]) = 'negative'"}>} [ID])

[Kushal_Chawda]

@ioannagr  try below

=count(DISTINCT {<ID=e({1<RESULT={'positive'}>}ID)>}ID)

[Kushal_Chawda]

@ioannagr  changed function concat to count

[ioannagr]

hello @Kushal_Chawda , I will try it and let you know, but how is this taking into account the different Test types?

[ioannagr]

Hello  @sunny_talwar  this gives me correct results, but i didn't quite get how it works.

I found that RangeMaxString() returns the last value in the text sort order that it finds in the expression or field.

It checked if [result test 1] exists and if it's negative, next the same for [resutl test 2] etc?

Also, @Kushal_Chawda , waiting for your explanation as well, yours looks very nice as well, but i don't understand how it checks for different test types.

[sunny_talwar]

RangeMaxString() works similar to RangeMax(). So, if you do RangeMax(1, 2) = 2 and RangeMax(2, 1) = 2. Same way RangeMaxString(A, B) = B and RangeMaxString(B, A) = B. So, order doesn't matter but the value does.