Difference between 'set analysis' and 'if statement'

[#1]

Hi all,

I have been conducting a research for quite a while now about the difference between a set analysis and an if statement, and  yet to make any kind of conclusion.

To further understand my case, consider the following scenario:

• I want to do the following aggregation: sum(AmountEUR)
• The two tables of which I am using the fields are named: OTP and transactions
• The condition field is 'OTP.Ispaid' which means if an amound has been paid

The statements used are the following:

• If condition: sum (If(OTP.IsPaid=1,AmountEUR))
• Set analysis: sum( {<OTP.IsPaid={1}>}AmountEUR )

Each of the above statements returns a different value. Why? When should I use each of those statements ?

How does a set analysis statement work exactly ?

A sample of my data model has been attached for further clarifications.

Would appreciate any opinion on the matter,

Rawad @

[Peter_Cammaert]

All other conditions being equal, the two examples should return the same result. The difference in your examples resides mainly in efficiency and speed.In addition to that, set analysis is way more powerful for advanced aggregation than any IF THEN ELSE construct.

This is what happens when the two expressions are evaluated:

• Expression with IF: after deciding on which data rows are currently active ("selected"), the QlikView engine will visit every selected row with a value for field AmountEUR and evaluate the IF condition on that row and any associated data for example if OTP.IsPaid resides in another table). IF the result is different from NLL, it will be aggregated by way of a Sum() This may take a long time.
• Expression with set analysis: before the expression is ever evaluated, QlikView will reduce/expand the set of currently active ("selected") records so that they match the additional conditions imposed by the set analysis criteria. This happens only once, and before the chart/column/expression is evaluated/recalculated. Only then will the qlikview engine aggregate all AmountEUR values using the Sum() function. Much faster in almost all cases.

Best,

Peter

[#3]

Hi Peter,

If you check the attached qvw file, you can see that the two expressions do not return the same result.

Any explanation as to why ?

Thank you,

Rawad

[Peter_Cammaert]

That's not too difficult to find out. The IF() logic traces associations, while set analysis just reduces/expands the data set and goes on with the result in table Transactions.

For example, recreate your two tables but this time add a RowNo() column. Now for every internal table, create a table box that includes the RowNo column and each individual field. Select %RandomId = 0156510500311000. That's one of the Ids that is responsible for the difference between the two Sum() result.

Both tables have a lot of superfluous rows. The Transactions table contains 8 rows, of whuch only 4 contain an actual amount. The OTP table even has 6 identical rows for this RandomId. If you replay the IF approach, associative logic will combine 6 OTP records with 8 Transactions rows. But since three of the four amounts annihilate each other, you'll get 6 times a value of 0,89 = 5,34.

The set analysis approach just sums the 8 rows in Transactions and produces 0,89. IMHO the set analysis result is correct.

Eliminate all redundant rows, and the expressions will produce the same result.

Peter