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 · Accepted solution]

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,

[joe_de_foleschampe]

Thanks! This one is a bit tricky as I have dates in the time-series from 2000.1, 2000.2, 2000.3, 2000.4, ... ,2014.1 that are correct and I need to keep intact in the time series and can't replace from "20" to "19". So its just quarters that were miscoded as 2096.1, 2096.2, 2096.3, 2096.4, ... ,2099.1, 2099.2, 2099,3, 2099.4 that need to be recoded. Maybe if I have two input tools with the same file and build a workflow #2 to handle the "20" to "19" replacement and then bring them back into the correct time-series, being workflow #1. Will give it a try later tonight.

[Thableaus]

@joe_de_foleschampe 

Check my edited post. I reviewed these cases.

Cheers,

[joe_de_foleschampe]

OK, great, will give that a go, too! Much appreciated!

[joe_de_foleschampe]

Perfect! Worked perfectly! Thank you and have a great weekend!