How to create a calculated column by comparing string values from another column in Tibco spot fire

Question

can some one please help me to create a calculated column as explained below?

consider the table

col1 | col2 | col3
   1 |   x  |  ER
   1 |   x  |  IG
   1 |   x  |  C
   1 |   y  |  ER
   1 |   y  |  ER
   2 |   y  |  IG
   2 |   y  |  C
   2 |   y  |  ER
   2 |   z  |  ER
   2 |   z  |  IG

I need a calculated column which says 'success' if there exists at least one 'C' on col3 for group of col1 and col2, else 'fail'.

so my new table must look like:

col1 | col2 | col3 | calculated_col
   1 |   x  |  ER  |  success
   1 |   x  |  IG  |  success
   1 |   x  |  C   |  success
   1 |   y  |  ER  |  fail
   1 |   y  |  ER  |  fail
   2 |   y  |  IG  |  success
   2 |   y  |  C   |  success
   2 |   y  |  ER  |  success
   2 |   z  |  ER  |  fail
   2 |   z  |  IG  |  fail

that is:

1. for the combination of 1 and 'x' from col1 and col2 respectively there is at leat one 'c' at col3, so expression result is 'success'
2. for the combination of 1 and 'y' from col1 and col2 respectively there is no 'c' at col3, so the expression result is 'fail'
3. for the combination of 2 and 'y' from col1 and col2 respectively there is at least one 'c' at col3, so expression result is 'success'
4. for the combination of 2 and 'z' from col1 and col2 respectively there is no 'c' at col3, so the expression result is 'fail'

Answer 1

you can use the Intersect OVER function to gather values in cells according to a hierarchy you define (in this case, col1>col2).

the following expression produces your desired results:

If(
  Find("C", UniqueConcatenate([col3]) OVER (Intersect([col1], [col2]))) > 0,
   "success","fail"
)

it's not particularly robust, however: if you have any other values in col3 that contain "C", this expression will evaluate to "success"!