What is this formula doing

[Mike Bergen]

What exactly is this formula doing If(Sum(if([TASK_TYPE_CD] in ("COEIEQP", "NIDCOEI"),1,0)) over ([DOCUMENT_NO])>0,True,False) Can you break this down for me please

[Harshith Reddy Musku]

For each unique value in[DOCUMENT_NO] it checks if correponding value in[TASK_TYPE_CD] column is either"COEIEQP" or "NIDCOEI" and assigns 1 or 0 accordingly. It will then sum up all the combinations for a single unique value in [DOCUMENT_NO] and checks if it is greater than 0

Eg:

[DOCUMENT_NO] [TASK_TYPE_CD] Sum(if([TASK_TYPE_CD] in ("COEIEQP", "NIDCOEI"),1,0)) over ([DOCUMENT_NO])

a COEIEQP 2

b CCCCCC 0

a NIDCOEI 2

c COEIEQP 1

b DDDDDD 0

 

 

 

In the above example, there are 3 unique [DOCUMENT_NO] a, b, c. There are both occurences of a and both the corresponding values in[TASK_TYPE_CD] arein ("COEIEQP", "NIDCOEI"). So it will sum up to 2 and hence 'TRUE' . Both the corresponding values for bin[TASK_TYPE_CD] are not in ("COEIEQP", "NIDCOEI"), so sum is 0 and hence 'False'