Forum Discussion

jitpbi's avatar
jitpbi
Icon for Post Patron rankPost Patron
6 years ago
Solved

Calculate status

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:

 

Output1Output2StatusNew Status
100150GoodGood
00GoodError
0100GoodError
5050NormalNormal
120130NormalNormal

 

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

  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    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

5 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    jitpbi , You can create a new column like

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Based on your conditions, the output in the 3rd row of your table is wrong. It should be Good, not Error.
    • jitpbi's avatar
      jitpbi
      Icon for Post Patron rankPost Patron
      6 years ago
      Anonymous Actually, the status is determined based on the values not on the pattern. Thanks
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.