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)

Answer 1

(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)