topic Re: If ..then ..else .. in an expression possible ? in QlikView

If ..then ..else .. in an expression possible ?

claudialet — Wed, 25 Nov 2020 16:16:04 GMT

So I have two conditional flag s

If ForeignFlag =1 show foreign values ... if there are no foreign values then..

If StateFlag = 0 show state values

How do I incorporate this into the expression with else statement ?

if($(vFilter)=1 and ForeignFlag=1 , EntityNumber &'-'& EntityName ,

if($(vFilter)=4 and EntityTypeFlag=1, EntityNumber &'-'& EnterpriseName,))

Re: If ..then ..else .. in an expression possible ?

PauloHSantos — Mon, 04 Feb 2019 20:27:24 GMT

For this, rather than an if you can just use "set analysis" to get what you whant this can work like an if or where(from SQL)

Re: If ..then ..else .. in an expression possible ?

stevedark — Mon, 04 Feb 2019 22:09:13 GMT

Hi,

If, then, else is totally possible, but it differs a bit depending on where you are doing it.

Are these fields in a table you are loading? If so the syntax is a bit like this:

LOAD
if(ForeignFlag = 1, [Foreign Values], if(StateFlag=0, [State Values], 'Neither Foreign or State')) as [Field Name],

If you are doing it in a chart expression it is very similar, except you would not have the "as [Field Name],". You would also typically have an aggregation, such as a Sum, to give you totals:

sum(if(ForeignFlag = 1, [Foreign Values], if(StateFlag=0, [State Values], 0)))

The load script also has VB style if then else blocks:

if ForeignFlag = 1 then
LOAD
ForeignValues...
else
if StateFlag = 0 then
LOAD
StateValues...
end if
end if

The code you posted looks fine, but to have a null value returned if neither expression is true you either need to lose that last comma, or have ", null()". Having the comma without a value after may cause it to break.

Hope that helps.

Steve

Re: If ..then ..else .. in an expression possible ?

Peter_Cammaert — Tue, 05 Feb 2019 08:54:26 GMT

If I may add to an issue that has been expertly handled already, in QlikView script there are two IF constructs:

The IF statement is to be used on the outer statement level, even inside other IF statements. It follows the classical IF THEN ELSE END IF format that is common in other programming languages.
The IF() function is to be used in all expressions (you can't use an IF statement in an expression). It follows regular calling conventions with parameters: 2 are fixed (the condition and the true-expression), 1 is optional (the false-expression)

As a result, you can only mingle IF functions in column expressions in a LOAD statement. You cannot use IF THEN ELSE inside a LOAD statement. The net result on the other hand is identical: conditional execution/evaluation.