qlikview - count based on a count condition

Question

I have the following table.

order_number
100
101
101
102
103
103
103
104
104

I'd like to create an expression which counts instances where order_number occurs only once. Example - 100 and 102 do not repeat, while 101, 103 and 104 have multiple instances. The expected result is 2.

I've tried count({<count(order_number)={1}>}order_number) but does not seem to work.

Tried using Aggr too count(({<Aggr(count(order_number), order_number))={1}>}order_number)

And the dimension is `order_number` itself? – Stefan Stoichev Jun 20 '22 at 16:04 — Stefan Stoichev, Jun 20 '22 at 16:04
@StefanStoichev Yes, that is correct. Have only one column. – Bhargav Gorantla Jun 20 '22 at 16:28 — Bhargav Gorantla, Jun 20 '22 at 16:28

score 0 · Accepted Answer · answered Jun 20 '22 at 19:27

(Probably not very efficient way)

sum( if( aggr( count( order_number ), order_number) = 1, 1, 0 ) )

(from inside to outside)

aggr( count( order_number ), order_number) - will count the instances of each order_number
if( ... = 1, 1, 0) - for each order_number if the inner (count) calculation is equal to 1 then return 1 else 0
and sum(...) all of this

And the result (in the textbox)

If possible (depends on your requirement) flag these records in the script. After that the expression will be much simple (and efficient)

qlikview - count based on a count condition

1 Answers1