topic Scripting / Expression assistance with Count Distinct in QlikView

Scripting / Expression assistance with Count Distinct

QFanatic — Sat, 16 Nov 2024 01:08:00 GMT

Hi all

In my model I am reading a 'main' table, and 3 subsequent other tables.

Now, a record can exists in one of these 3 tables, or two, three, or none at all.

if it exists in at least one table, I want to count the distinct value ONCE only . in other words, if count distinct (field) = 0, value returns 0, ELSE return ONLY 1.

My problem is, if I count the distinct value in my expression (which exists in 2 of these tables), it ONLY gets the right answer if Customer is a dimension in the table. If I remove the Customer column, it gets incorrect value.

Please assist - might be better doing this in the script? I dont want to join the tables, since later on, I need to add more details to the model and then a join might not work like I want it to.

(Data has been scrambled for security)

Much appreciated

Re: Scripting / Expression assistance with Count Distinct

StarinieriG — Wed, 04 Mar 2020 16:04:09 GMT

Hi,

I'm not sure, but have you tried to use aggr?

Like this

=Sum(Aggr(if(RangeSUM(PROFILE_MOBI,PROFILE_OL,PROFILE_BA) > 0,
IF(CUS_TYPE_MOBI= 'ZOB' OR CUS_TYPE_OL= 'ZOB' OR CUS_TYPE_BA= 'ZOB',
COUNT({<CO_CDE = {'15'}>} distinct CUSTOMERS)
)
),CUSTOMERS,PERIOD))

Re: Scripting / Expression assistance with Count Distinct

QFanatic — Thu, 05 Mar 2020 05:54:11 GMT

Thank you - it works.