topic How to use a variable within an IF Statement? in QlikView

How to use a variable within an IF Statement?

Wed, 17 Aug 2011 19:41:03 GMT

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.

How to use a variable within an IF Statement?

erichshiino — Wed, 17 Aug 2011 19:45:12 GMT

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

How to use a variable within an IF Statement?

Wed, 17 Aug 2011 19:45:32 GMT

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

How to use a variable within an IF Statement?

Wed, 17 Aug 2011 19:49:43 GMT

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

How to use a variable within an IF Statement?

Wed, 17 Aug 2011 20:39:28 GMT

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

How to use a variable within an IF Statement?

Wed, 17 Aug 2011 20:51:31 GMT

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

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

How to use a variable within an IF Statement?

erichshiino — Wed, 17 Aug 2011 21:01:12 GMT

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