How access the name of the column when writing formulas

Hi there. I have a table like this. It has columns in names of months that serve as a sparse matrix. What I plan to do is for each row, I need to find the column with its name equals the value of [FCST_Month] and see if it's value is null. For example, for row #1 I want to check column [May] and see if the value is null or not. Does anyone know how to do that? I don't know how to access the column name. Thanks in advance!

#	FCST_Month	May	June	July	August
1	May	Null	200	Null	100
2	June	Null	200	Null	100
3	July	Null	Null	Null	Null
4	August	Null	Null	Null	Null

Thanks,

Yiying

Developer Tools

Accepted answers

Here's how I would approach this question:

- Tranpose your Month Fields grouping by # and FCST_Month

- Create the Field "Null or Not" with the special condition:

If [FCST_Month] = [Name] and [Value] != "Null"
THEN "Not Null"
ELSEIF [FCST_Month] = [Name] and [Value] = "Null"
THEN "Null"
ELSE "" ENDIF

- Filter the non-empty Records

- Join the result back to the original not-transposed dataset

WF attached.

Cheers,

FieldConditions.yxmd

All comments

Here's how I would approach this question:

- Tranpose your Month Fields grouping by # and FCST_Month

- Create the Field "Null or Not" with the special condition:

If [FCST_Month] = [Name] and [Value] != "Null"
THEN "Not Null"
ELSEIF [FCST_Month] = [Name] and [Value] = "Null"
THEN "Null"
ELSE "" ENDIF

- Filter the non-empty Records

- Join the result back to the original not-transposed dataset

WF attached.

Cheers,

FieldConditions.yxmd

Another thing to add:

I actually set the condition to be = "Null" because the Null values are stored as strings.

If they are actually Null values, you should use the IsNull() function.

Cheers,

Thanks for the help, that works perfectly!

Yiying

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15880

MarqueeCrew 13708

apathetichell 13703