Calculate status

[jitpbi]

Hi,

I need help to get the status of a field based on few conditions. The below is the sample data where "New Stats" is the required field:

Output1	Output2	Status	New Status
100	150	Good	Good
0	0	Good	Error
0	100	Good	Error
50	50	Normal	Normal
120	130	Normal	Normal

Status field has only 2 values Good and Normal but i need to calculate 3 values in Status field based on the below conditions:

If Output1 < 5 and Output2 > 100 then "Error"

   Output1 = 0 and Output2 = 0 then "Error" 

other then above scenarios my new status field should pick the same value of the Status field. 

Thanks

[Anonymous]

Based on your conditions, the output in the 3rd row of your table is wrong. It should be Good, not Error.

[jitpbi]

@Anonymous Actually, the status is determined based on the values not on the pattern. Thanks

[Anonymous]

Here's your rules:

If Output1 < 5 and Output2 > 100 then "Error"
Output1 = 0 and Output2 = 0 then "Error"
other then above scenarios my new status field should pick the same value of the Status field.

Now apply those to the table you've posted and you'll see I'm right. I don't even understand what you mean by saying "the status in determined based on the values not on the pattern." What pattern are you talking about? The only thing that is used to derive the values is the rules above.... No pattern in sight.

[amitchandak]

@jitpbi , You can create a new column like

New Status =
Switch(true(),
[Output1] < 5 && [Output2] >= 100 , "Error",
[Output1] =0 && [Output2] = 0 , "Error",
"Normal"
)

[AntrikshSharma · Accepted solution]

@jitpbi  Try this:

New Status =
VAR FirstOutput = Table[Output1]
VAR SecondOutput = Table[Output2]
VAR CurrentStatus = Table[Status]
VAR Result =
    IF (
        OR (
            AND ( FirstOutput < 5, SecondOutput > 100 ),
            AND ( FirstOutput = 0, SecondOutput = 0 )
        ),
        "Error",
        CurrentStatus
    )
RETURN
    Result