Formula with Set Analysis different than without set analysis

[Touix]

Hi Everyone, 

I hope you are OK.

I demand your help because I do not understand one of Qlikview behaviour.

My need is to store some variables results into a QVD files in order to accelerate the speed of the reporting.

Indeed, I have too many data so i want to have an intermediate step. But it is not simple because i need to rewrite my variables that contain SET ANALYSIS because it is not accepted into load script.

But when I modify my formula.... i have completely different result. 

My example :

sum ( {<
A.ANNEE={'2021'},
B.TYPE_B={'ENC'},
C.TYPE_C={'OPT'},
D.TYPE_D={"<>0"},
E.TYPE_E -= {'S S'}
>} F.QTY)

RESULT : 13 000

Rewrite in : 

sum (
if(A.ANNEE='2021' and B.TYPE_B='ENC'  and C.TYPE_C='OPT' and D.TYPE_D<>0
E.TYPE_E <> 'S S'
,F.QTY,0)
)

RESULT : 40 000

But I have exactly the same result without this line : 

E.TYPE_E -= {'S S'}

Where can be the issue please ?

I am sorry I can not share the data... I have tooo many lines.

Thanks thanks thanks for your help

[Or]

Some of your fields might have null values, in which case they'll get filtered out by the set analysis but not by the if() statements. That's the first place I'd check.

[agigliotti]

Hi @Touix ,

Give a try with the below change:

sum ( {<
A.ANNEE={'2021'},
B.TYPE_B={'ENC'},
C.TYPE_C={'OPT'},
D.TYPE_D -= {'0'},
E.TYPE_E -= {'S S'}
>} F.QTY)

I hope it can helps.

Best Regards
Andrea

[Touix]

Ow ! Is there any clue to bypass this null value  like the set analysis ?

[Touix]

Same results 😞

[Touix]

But I have exactly the same result without this line : 

Without set analysis : 

E.TYPE_E <> 'S S'

Do you know what can be the difference between Set Analysis and IF ? Specially for the join part ?

[agigliotti]

there is a mistake in the expression syntax:

sum (
if(A.ANNEE='2021' and B.TYPE_B='ENC' and C.TYPE_C='OPT' and D.TYPE_D<>0 and
E.TYPE_E <> 'S S'
,F.QTY,0)
)

there was an "and" operator missing...

[Or]

Again, check if any null values exist in the field E.TYPE_E. If any null values exist, they will be filtered out by the set analysis but not by the IF() syntax, I believe.

[Touix]

Sorry my bad.

I forgot an 'and'

[rwunderlich]

"E.TYPE_E <> 'S S'"  is not exactly the same as "E.TYPE_E -= {'S S'}". It is the same as "E.TYPE_E = - {'S S'}" (note the position of the "-").

Are the fields in different or the same tables?

-Rob