Multiple IIF Statements The Right Way To Go?

joe_de_foleschampe

Hi - can't seem to find a solution: I am evaluating a time-series and replacing erroneously coded quarterly dates and adding a column that has the corrected time periods. So, for example, if the data set has a value of 2096.1, then it is re-coded correctly to 1996.1 which is the correct period.

This works one off

IIF([YYYY.Q_INIT]=2096.1,"1996.1",0)

So I am trying to replace in some type of nested IIF statement or equivalent to handle all these time periods in one shot, so the output hits a single, new output column I specified in the "Output Column" configuration as "YYYY.Q_CORR"

2096.1 to 1996.1

2096.2 to 1996.2

2096.3 to 1996.3

2096.4 to 1996.4

2097.1 to 1997.1

2097.2 to 1997.2

2097.3 to 1997.3

2097.4 to 1997.4

Thanks for any help!

Thableaus

Hi @joe_de_foleschampe 

I'd recommend you to use Replace Function.

EDIT: Here's a more precise way to do it, assuming your data doesn't have year values over 2050.

IF ToNumber(LEFT([YYYY.Q_INIT], 4)) > 2050 THEN

Replace([YYYY.Q_INIT], "20","19")

ELSE [YYYY.Q_INIT] ENDIF

Cheers,