Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
if(isnull(EndDate),varToday,EndDate) as ModifiedEndDate should do the trick in your load script.
Thanks a lot Erich and Leonard...Have a good one!!
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
You will need to apply the same logic on the EndDate field once more:
EmployeeID&'/'&StartDate&'/'&if(isnull(EndDate),today(),EndDate) as EmployeeIntervalKey
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