Alter value based on other columns

Question

Hello everyone,&nbsp;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 blank, then it's 0&nbsp;IDScoreIssue1Issue2Issue3FILTERED_SCORE100110&nbsp;&nbsp;&nbsp;10100120Reason1&nbsp;&nbsp;0100120&nbsp;&nbsp;&nbsp;20100210&nbsp;Reason3&nbsp;0100210&nbsp;&nbsp;&nbsp;10100220&nbsp;&nbsp;Reason20

rsbin · Accepted Answer

KPME ,
You can use the SWITCH function in combination with OR:
Filtered_Score1 = SWITCH(
                    TRUE(),
                 OR( [Issue3]&lt;&gt; BLANK(), OR( [Issue1] &lt;&gt; BLANK(), [Issue2] &lt;&gt; 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
&nbsp;
&nbsp;
&nbsp;
10
10

1001
20
Reason1
&nbsp;
&nbsp;
0
0

1001
20
&nbsp;
&nbsp;
&nbsp;
20
20

1002
10
&nbsp;
Reason3
&nbsp;
0
0

1002
10
&nbsp;
&nbsp;
&nbsp;
10
10

1002
20
&nbsp;
&nbsp;
Reason2
0
0

Regards,

Forum Discussion

Alter value based on other columns

2 Replies

Related content

SQL ALTER command

Altering data to tur row values in to columns

Splitting Column values

Matching Column Values

Data Warehouse Alter Table add default value constraint

ID	Score	Issue1	Issue2	Issue3	FILTERED_SCORE
1001	10				10
1001	20	Reason1			0
1001	20				20
1002	10		Reason3		0
1002	10				10
1002	20			Reason2	0