topic Re: Order of modifiers in element function, E() produce different results? in QlikView

Order of modifiers in element function, E() produce different results?

Astelmach — Sat, 16 Nov 2024 00:48:41 GMT

Hi,

I have been working with various set expressions with large data sets (10mill+ row) and I found that set expressions produce various results depending of the order of set modifiers within element function, E() and the data model.

I have created a small sample set to reproduce the case with only 10 data rows,
using two different models.

Model 1: - single fact table
Model 2: - two tables linked with common field, OrderId.

I have used two different formulas to calculate the number of orders,
fullfilling the critreria, with the only difference being order of the set modifiers.

Formula 1:

count (DISTINCT {$<Status={"Completed"},OrderId = E({
1<OrderedBy={"Z"}> +1 <SubOrderCounter={">=2"}>}) >} OrderId)

Formula 2:

count (DISTINCT {$<Status={"Completed"},OrderId = E({
1 <SubOrderCounter={">=2"}> +1<OrderedBy={"Z"}> }) >} OrderId)

In Data Model 2 I get result = 5, while I was expecting =4, which would be inline with data model 1.

I have spend lots of time analysing the issue, but I can't figure it out why different results are produced.
Matematically - adding two different sets I form a larger union between them
and the order should not matter.
What have I omitted?

I would appreciate guidance from the experts in the community.

Best regards,
Adam

Code for the data models:

QUALIFY *;
O:
Load * Inline
[OrderId, SubOrderCounter, OrderedBy, Status,Delivery
1, 1, A, Ordered, Preliminary
2, 1, B, Ordered, Preliminary
3, 1, C, Completed, Delivered
4, 1, D, Completed, Delivered
5, 1, E, Completed, Delivered
6, 1, F, Completed, Delivered
7, 2, A, Cancelled, Cancelled
8, 2, A, Completed, Delivered
9, 2, Z, Completed, Delivered
10,2, Z, Completed, Delivered
];

UNQUALIFY *;

Orders:
Load * Inline
[OrderId, Status,Delivery
1, Ordered, Preliminary
2, Ordered, Preliminary
3, Completed, Delivered
4, Completed, Delivered
5, Completed, Delivered
6, Completed, Delivered
7, Cancelled, Cancelled
8, Completed, Delivered
9, Completed, Delivered
10,Completed, Delivered
];

OrderData:
Load * Inline
[OrderId, SubOrderCounter, OrderedBy
1, 1, A
2, 1, B
3, 1, C
4, 1, D
5, 1, E
6, 1, F
7, 2, A
8, 2, A
9, 2, Z
10,2, Z
];

The samples app is attached as well.

Re: Order of modifiers in element function, E() produce different results?

Astelmach — Sun, 24 May 2020 13:42:31 GMT

Hi,

I lokked into this problem a bit more. So far I have not been able to find an answer to this problem. The associated data model (two-tables) is not able to exclude the order that fulfills the criteria of element function.

I have even excluded the searching with "", but results are the same.

count (DISTINCT {$<
Status={Completed}
,OrderId = E({
<OrderedBy={Z}>
+ <SubOrderCounter={2}>
})
>} OrderId)

Mathematically the + sign between modifiers builds a new union between the two modifiers. Order - orderedBy by Z having subOrderCounter = 2 should be in that set and hence be excluded.

The workaround is the use negating modifier outside the element function.
count (DISTINCT {$<
Status={Completed}
,SubOrderCounter-={2}
,OrderId = E({
<OrderedBy={Z}>
+ <SubOrderCounter={2}>
})
>} OrderId)

This gives the correct result.

Has anybody experienced similar issue and how to solve it?

@hic Henric - would you be able to comment this?

Best regards,
Adam

Re: Order of modifiers in element function, E() produce different results?

mikaelsc — Sun, 24 May 2020 18:59:25 GMT

shouldn't set operators e() be used like this:

count (DISTINCT {$<Status={"Completed"},OrderId = E({1<SubOrderCounter = {">=2"}>}) + E({1<OrderedBy = {'Z'}>})>}OrderId)

additionnally... expected result would be 5?

E({1<SubOrderCounter = {">=2"}>}) == {1,2,3,4,5,6}

E({1<OrderedBy = {'Z'}>}) == {1,2,3,4,5,6,7,8}

==> {1,2,3,4,5,6,7,8}

completed = {3,4,5,6,8,9,10}

==> orderID in final set = {3,4,5,6,8}

I have no idea what results are calculated with you expressions... but yes... they give unexpected results. (but your expressions are also unexpected 😉 )