Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Scripting / Expression assistance with Count Distinct

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

2 Replies
Highlighted
Partner
Partner

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

View solution in original post

Highlighted
Creator
Creator

Thank you - it works.