Forum Discussion

KPME's avatar
KPME
New Member
3 years ago
Solved

Alter value based on other columns

Hello everyone,   basically I have a table that is the below. The "FILTERED_SCORE" is what I'm trying to achieve. Basically: FILTERED_SCORE = "Score" unless Issue1, Issue 2 or Issue 3 are not bla...
Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    KPME ,

    You can use the SWITCH function in combination with OR:

    Filtered_Score1 = SWITCH(
                    TRUE(),
                 OR( [Issue3]<> BLANK(), OR( [Issue1] <> BLANK(), [Issue2] <> BLANK() )), 0,
                 [Score] )

    The "Or" function only accepts 2 arguements, so I nested them together to capture all 3 Issue columns.

    IDScoreIssue1Issue2Issue3FILTERED_SCOREFiltered_Score1

    1001 10       10 10
    1001 20 Reason1     0 0
    1001 20       20 20
    1002 10   Reason3   0 0
    1002 10       10 10
    1002 20     Reason2 0 0

    Regards,

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
3 years ago

KPME ,

You can use the SWITCH function in combination with OR:

Filtered_Score1 = SWITCH(
                    TRUE(),
                 OR( [Issue3]<> BLANK(), OR( [Issue1] <> BLANK(), [Issue2] <> BLANK() )), 0,
                 [Score] )

The "Or" function only accepts 2 arguements, so I nested them together to capture all 3 Issue columns.

IDScoreIssue1Issue2Issue3FILTERED_SCOREFiltered_Score1

1001 10       10 10
1001 20 Reason1     0 0
1001 20       20 20
1002 10   Reason3   0 0
1002 10       10 10
1002 20     Reason2 0 0

Regards,

  • KPME's avatar
    KPME
    New Member
    3 years ago

    You're the best! worked like a charm 😄