topic Re: Formula with Set Analysis different thant without set analysis in QlikView

Formula with Set Analysis different than without set analysis

Touix — Wed, 31 Mar 2021 13:22:47 GMT

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

Re: Formula with Set Analysis different thant without set analysis

Or — Wed, 31 Mar 2021 13:11:11 GMT

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.

Re: Formula with Set Analysis different than without set analysis

agigliotti — Wed, 31 Mar 2021 13:14:49 GMT

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

Re: Formula with Set Analysis different thant without set analysis

Touix — Wed, 31 Mar 2021 13:15:46 GMT

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

Re: Formula with Set Analysis different than without set analysis

Touix — Wed, 31 Mar 2021 13:20:58 GMT

Same results 😞

Re: Formula with Set Analysis different than without set analysis

Touix — Wed, 31 Mar 2021 13:25:40 GMT

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 ?

Re: Formula with Set Analysis different than without set analysis

agigliotti — Wed, 31 Mar 2021 13:45:06 GMT

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

Re: Formula with Set Analysis different than without set analysis

Or — Wed, 31 Mar 2021 13:58:57 GMT

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.

Re: Formula with Set Analysis different than without set analysis

Touix — Wed, 31 Mar 2021 14:47:50 GMT

Sorry my bad.

I forgot an 'and'

Re: Formula with Set Analysis different than without set analysis

rwunderlich — Wed, 31 Mar 2021 22:20:38 GMT

"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

Re: Formula with Set Analysis different than without set analysis

Touix — Thu, 01 Apr 2021 06:02:36 GMT

E is another table.

Is there any solution to sotre the value of a variable write with set analysis into a QVD or other sheet ?

Re: Formula with Set Analysis different than without set analysis

marcus_sommer — Thu, 01 Apr 2021 09:15:29 GMT

Store a variable result with set analysis into a f... - Qlik Community - 1796268