Higher value in new output column

Question

Hi,

Need help in solving below problem.

In the below example table,

Score 1 & 2 will have only 100 or 0 and

Score 3, 4, 5 & 6 will have only 90 or 0

I need the higher value in the new Final Score column and If both 100 and 90 are not available then the final value should be '0'.

Please refer to the attached sample file below and advise on the same. Thanks

Scoring sample.xlsx

danilang · Answer

Hi @PrasannaKumar

If your problem is really as you state with static columns and there are no other conditions, you can solve it using the Max() function in a single formula tool

Max([Score 1], [Score 2],[Score 3],[Score 4],[Score 5],[Score 6])

It returns the maximum value of the input fields

The Select tool is just there to remove the existing [Final Score] field

Dan

HigherValue.yxzp

rkapoor · Answer

@PrasannaKumar have a look at the attached workflow.

Here, you flip the data using the Transpose so that all values are in 1 column.  You use the Summarize to calculate the Maximum Score values and then bring back with the data to assign it via the Join.

The Data Cleanse deals will the NULLs ie. if not 100 or 90, and sets to 0. As your sample data only had 100 or 90 or 0, you can build a formula to deal with other values if required in a Formula tool.

Flip the data back with the Crosstab to get your output.

PrasannaKumar.yxzp

bensilv · Answer

Hi @PrasannaKumar

A few ways you could do this, one to add a record ID, transpose and then using the summarise tool to take a MAX across all fields

Please see attached workflow

Ben

Max Score.yxmd