Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Astelmach
Contributor
Contributor

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

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


overview.png

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.

Labels (1)
2 Replies
Astelmach
Contributor
Contributor
Author

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

mikaelsc
Specialist
Specialist

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 😉