Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Touix
Contributor III
Contributor III

Formula with Set Analysis different than without set analysis

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

 

 

 

 

 

11 Replies
Or
Master
Master

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
Partner
Partner

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
Contributor III
Contributor III
Author

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

Touix
Contributor III
Contributor III
Author

Same results 😞

Touix
Contributor III
Contributor III
Author

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
Partner
Partner

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
Master
Master

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
Contributor III
Contributor III
Author

Sorry my bad.

I forgot an 'and'

rwunderlich
Luminary Alumni
Luminary Alumni

"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