Hi all, I have the below data and I would like to create a new column which states:IF Rate1TypeName or Rate2TypeName or Rate3TypeName = "CUK Vehicle Rent - Week"THEN return the value in the field in the column next to the column that had "CUK Vehicle Rent - Week" So for example it would be called "CUK Vehicle Rent - Week Pay Rate" And for Candidate Ref 35452( Row 3) it would be-35 because that is the value adjacent to the type column that had "CUK Vehicle Rent - Week" Hope that makes sense Rate1TypeNameRate1PayRateRate2TypeNameRate2PayRateRate3TypeNameRate3PayRateHourly STD Rate15.68Bonus400 Hourly STD Rate15.68Bonus400 Shifts - Standard270CUK Vehicle - Hours0CUK Vehicle Rent - Week-35Standard Rate27.5CUK Vehicle - Hours0 Standard Rate27.5CUK Vehicle - Hours0 Standard Rate27.5CUK Vehicle - Hours0 Standard Rate27.5CUK Vehicle - Hours0 Standard Day Rate28CUK Vehicle - Hours0 Standard Rate28.5CUK Vehicle - Hours0 Standard Day Rate28CUK Vehicle - Hours0 Standard Day Rate28CUK Vehicle - Hours0 Standard Day Rate28CUK Vehicle - Hours0 Standard Rate330CUK Vehicle - Shifts0 Standard Rate330CUK Vehicle - Shifts22 Standard Rate330CUK Vehicle - Shifts22 Standard Rate29CUK Vehicle Rent - Week-110 Standard Rate29CUK Vehicle Rent - Week-110

HenryJS

Post Prodigy

6 years ago

Solved

New Column: IF, Return

Hi all,

I have the below data and I would like to create a new column which states:

IF Rate1TypeName or Rate2TypeName or Rate3TypeName = "CUK Vehicle Rent - Week"
THEN return the value in the field in the column next to the column that had "CUK Vehicle Rent - Week"

So for example it would be called "CUK Vehicle Rent - Week Pay Rate"

And for Candidate Ref 35452( Row 3) it would be-35 because that is the value adjacent to the type column that had "CUK Vehicle Rent - Week"

Hope that makes sense

Rate1TypeName	Rate1PayRate	Rate2TypeName	Rate2PayRate	Rate3TypeName	Rate3PayRate
Hourly STD Rate	15.68	Bonus	400
Hourly STD Rate	15.68	Bonus	400
Shifts - Standard	270	CUK Vehicle - Hours	0	CUK Vehicle Rent - Week	-35
Standard Rate	27.5	CUK Vehicle - Hours	0
Standard Rate	27.5	CUK Vehicle - Hours	0
Standard Rate	27.5	CUK Vehicle - Hours	0
Standard Rate	27.5	CUK Vehicle - Hours	0
Standard Day Rate	28	CUK Vehicle - Hours	0
Standard Rate	28.5	CUK Vehicle - Hours	0
Standard Day Rate	28	CUK Vehicle - Hours	0
Standard Day Rate	28	CUK Vehicle - Hours	0
Standard Day Rate	28	CUK Vehicle - Hours	0
Standard Rate	330	CUK Vehicle - Shifts	0
Standard Rate	330	CUK Vehicle - Shifts	22
Standard Rate	330	CUK Vehicle - Shifts	22
Standard Rate	29	CUK Vehicle Rent - Week	-110
Standard Rate	29	CUK Vehicle Rent - Week	-110

HenryJS try adding a new column with the following expression

New Column = 
SWITCH ( TRUE(),
Table[Rate3TypeName] = "CUK Vehicle Rent - Week", Table[Rate3PayRate],
Table[Rate2TypeName] = "CUK Vehicle Rent - Week", Table[Rate2PayRate],
Table[Rate1TypeName] = "CUK Vehicle Rent - Week", Table[Rate1PayRate]
)

I would 💖 Kudos 🙂 if my solution helped. ?

Forum Discussion

New Column: IF, Return

5 Replies

Related content

Return Column Name if Zero Value

Creating custom columns keeps returning a structured Table column

If Statement Calculated Column Returning Incorrectly

Create measure to return value from column when condition met for two other columns

Need column to return date in working days from today, from query table column.