Need to insert a calculated column with most common values in two columns

[PR Bhat]

I have three column in which different values are available. Need to insert a new column which gives the result of most common values in Col2,Col3,Col4 as Col5 will be new calculated collmn based on most common values of Col2,Col3,Col4. Is it possible to insert column without pivot or upivot in the same table.

 

 

 

 

Col1

Col2

Col3

Col4

Col5

 

 

A

NA

X

X

X

 

 

B

X

Y

X

X

 

 

C

Y

Z

Z

Z

[Gaia Paolini]

This solution is not a looker but would work, provided you are always using columns [Col1],..,[Col4], otherwise you would need to make it slightly more complex.

You need to have access to the TERR_String(..) function

Create the following calculated column for [col5]:

TERR_String("output

[Fabian Duerr]

Gaia, I really like that approach. I couldn't think of a solution without writng my own data function. Or of course simply do an unpivot operation, calculated column, pivot operation.

 

So here your x = Concatenate([Col1],",",[Col2],",",[Col3],",",[Col4]). Right If you would work already with another calculated column that consists of Concatenate(...) then you probably could just use this column as your x. Sometimes I just prefer those extra steps as it makes everything more readable. But anyways... Great solution. I really need to work more with apply function.

[Gaia Paolini]

yes one could pre-calculate the concatenation, to make it more maintainable and maybe expand to different columns. The sapply was needed because of the way the data is passed into the TERR_string function, so the input1 column is the whole column of concatenations row by row.

[Fabian Duerr]

Yes, thank you. This is how I understood the function. Pretty advanced stuff :)