6 Replies Latest reply on May 8, 2018 8:16 AM by Jim Van Sistine

# Calculated Field for Multi Row Data Results

In my data set I have multiple rows for the same #. Each row represents a task that then contains a result. I would like to add a Calculated Field that says IF (Result) = "no" THEN "Rejected", but my problem is I want the new Calculated Field to say "Rejected" for each row of that # column that has a "no" and I am unsure how to do that. Does anyone have any ideas?

Current Data Set:

0001     Open      yes

0001     Close     no

0001     Remove yes

0001     Cancel   yes

0002     Open     yes

0002     Close     yes

0003     Open      no

0004     Open      no

0004     Close      yes

0005     Open      yes

0005     Close      yes

0005     Remove  yes

0005     Cancel    yes

Desired Data Set:

#           Task   Result   New Calculated Field Here

0001     Open      yes    Rejected

0001     Close     no      Rejected

0001     Remove yes    Rejected

0001     Cancel   yes    Rejected

0002     Open     yes    Null

0002     Close     yes    Null

0003     Open      no     Rejected

0004     Open      no     Rejected

0004     Close      yes   Rejected

0005     Open      yes    Null

0005     Close      yes   Null

0005     Remove  yes   Null

0005     Cancel    yes   Null

• ###### 1. Re: Calculated Field for Multi Row Data Results

Try something like the below - it may need some work probably, but maybe it'll help you get started

{fixed task # : MIN(if result = 'no' then 'Rejected' else NULL)}

• ###### 2. Re: Calculated Field for Multi Row Data Results

Hi Trisha,

You can accomplish this with a Level of Detail calculation.

Which yields 1 result per value in the # (or Nbr in my case) in the data.

Hope this helps!  Let me know if you have any questions.

- Jim

• ###### 3. Re: Calculated Field for Multi Row Data Results

Thank you for your help! The new Calculated Field I added is called TASK RESULT REJECTION (see below). I continue to get Nulls using the formula.

Any ideas?

Formula:

IF {fixed [ECN] : min([Task_Result])} = "Rejected"

THEN "Rejected"

END

Data with Calculated Field:

• ###### 4. Re: Calculated Field for Multi Row Data Results

Trisha,

The values in your Task_Result field are different from the yes/no value in the sample, so the formula will have to be tweaked a little to make it work with this data.  For example, the min(Task_Result) for ECN = 1625 is 'Completed', which is why you are getting a Null result.

With this data, I would try a different formula.

IF

{fixed [ECN]: max(if [Task_Result]='Rejected' then 1 else 0 end)} =1

then 'Rejected'

END

The FIXED formula (inside the curly braces) resolves to 1 if the ECN contains a row noted as Rejected and 0 if it does not.   I had to do this since you effectively want all of the Task_Result values that are not 'Rejected' to be treated the same.  The IF/THEN is then looking for the value of 1 in that result and calling it Rejected.

Hope that helps!

- Jim

1 of 1 people found this helpful
• ###### 5. Re: Calculated Field for Multi Row Data Results

YAYYYY!!!! Thanks so much! This worked!