How to use a variable within an IF Statement?

[#1]

I have a table with field names EndDate, StartDate and EmployeeID. For some of the employees there is no EndDate and I would like to use varToday (I created this variable) where an EndDate is not present. I would like to load in my data with an IF Statement. Could any please help me with constructing this IF Statement. Thanks.

[erichshiino · Accepted solution]

Hi,

in your load script, where you have:

Load EndDate, StartDate, EmployeeID

From ...

You can use (to get the system today)

Load if( isnull(EndDate), today(), EndDate) as EndDate,

StartDate, EmployeeID

From ...

or (to use your variable)

Load if( isnull(EndDate), $(varToday), EndDate) as EndDate,

StartDate, EmployeeID

From ...

depending on the format you used inside varToday, the code should be:

Load if( isnull(EndDate), '$(varToday)', EndDate) as EndDate,

StartDate, EmployeeID

From ...

Hope this helps,

Erich

[#3]

if(isnull(EndDate),varToday,EndDate) as ModifiedEndDate should do the trick in your load script.

[#4]

Thanks a lot Erich and Leonard...Have a good one!!

[#5]

Now I am facing a slight problem. With the field EmployeeID, EndDate and StartDate I created another field called EmployeeIntervalKey, but the field EmployeeIntervalKey is not incorporating the values coming from the variable varToday.

Syntax for the field EmployeeIntervalKey is as following:

EmployeeID&'/'&StartDate&'/'&EndDate as EmployeeIntervalKey

[#6]

You will need to apply the same logic on the EndDate field once more:

EmployeeID&'/'&StartDate&'/'&if(isnull(EndDate),today(),EndDate) as EmployeeIntervalKey

[erichshiino]

Hi,

Where exactly did you  include this?

if it is in the same load, the EndDate field was not created.

If this is the case, you should include the if statement again in this line:

EmployeeID&'/'&StartDate&'/'&if( isnull(EndDate), $(varToday), EndDate)  as EmployeeIntervalKey

Hope this helps,

Erich