How access the name of the column when writing formulas

[yiying]

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

[Thableaus · Accepted solution]

Hey @yiying 

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,

[Thableaus]

@yiying 

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,

[yiying]

Thanks for the help, that works perfectly!

Yiying