create virtual column with number of rows that match a condition

[XJ581G]

hello to every one , i like your help with the following :

i have a table like this:

ID	ESP	MAT	HIS	FCE	BIO	EDF	TEC
AA7887	5	6	7	5	5	5	6

what i want is to create a virtual column that make a list with the names of the columns that have a value equal to 5, in this case the list would be: ESP, FCE, BIO, EDF.

THANKS FOR THE HELP

[Suvrutt_Gurjar]

I believe at row level, the expression may not be very nice one.

You could try an expression of

LIST(

IFS([ESP]=5, "ESP" , 

IFS([MAT]=5, "MAT" , 

IFS([HIS]=5, "HIS" , 

IFS([FCE]=5, "FCE" , 

IFS([BIO]=5, "BIO" , 

IFS([EDF]=5, "EDF" , 

IFS([TEC]=5, "TEC" 

)

[XJ581G]

Thanks for the advice 

Its does not have to be at row Level, i thought this way would be easier. Do you have a sugestion where it would be easier , table Level or an action that calculate the field in other table ???

[Suvrutt_Gurjar]

That will depend on the data schema. You have given example of one record , so the solution/suggestion was for one row.

If you can share the exact structure of alternate data schema that you have in mind, the community could suggest for those options.

[XJ581G]

i'll try to explain better what i want to achieve

i have two tables: TableA and TableB, TableA have some fields that contains numbers values(Field1,Field2,Field3,Field4,Field5), i want to determine what columns have a value equal to 5, and with the execution of an action or a bot, insert rows into TableB in a way that the value inserted in the row of TableB be the name of the columns that are equal to 5

TableA

ID	Month	Field1	Field2	Field3	Field4	Field5
Abb671	Feb	5	8	5	5	7
dfff767	March	8	9	5	7	5
Fg671f	Abril	7	5	5	9	9

Result in TableB should be

ID	Feb	March	Abril
fd577dd	Field1	Field3	Field2
hjdkh66	Field3	Field5	Field3
ggf6445	Field4

[Go]

For example, the formula would be as follows.

CONCATENATE(IF([ESP]=5,"ESP,",""), IF([MAT]=5,"MAT,",""), IF([HIS]=5,"HIS,",""))

[XJ581G · Accepted solution]

It works thanks.