Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to use a variable within an IF Statement?

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.

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

How to use a variable within an IF Statement?

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

6 Replies
erich_shiino
Honored Contributor

How to use a variable within an IF Statement?

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

Not applicable

How to use a variable within an IF Statement?

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

Not applicable

How to use a variable within an IF Statement?

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

Not applicable

How to use a variable within an IF Statement?

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

Not applicable

How to use a variable within an IF Statement?

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

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

erich_shiino
Honored Contributor

How to use a variable within an IF Statement?

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

Community Browser