Deduplication of Rows

user5388961

Hi,

I have the following columns and the situation.

Total Customers: (Count distinct of cust no)

                                  channel    Values below

                                  Internet =  1 , null

                                  branch =   1, null

                                  mobile =   1, null

my report is

1) month,  total customers

201510 7000000

when i run another report with Channel Type (case statement) with Month and Total Customers

case statement for channel type column

case when internet =1 then internet

when branch =1 then branch

mobile =1 then mobile

else others end

2) month     channel type     total customers

201510    internet               3600000

201510     branch               2800000

201510    mobile                  600000

3) but if i choose total customers where branch =1 then it is giving 4400000, as the 2nd report is giving unique customers of  each channel

where as the 3 rd report gives the customers who use branch channel, these customers might also use Internet and mobile

if i want to get 4400000 for branch channels, how can i do this. if i choose the channel type attribute the column count the customers only once in any of the channel. but i want to count each customer once if  customer use 3 channels.

please let me know any ideas.

Azhar01

Hi     Is the case statement correct. ?

case when internet =1 then internet

when branch =1 then branch

mobile =1 then mobile

else others end

or it should be

case when channel _type =1 then internet .... like that

Thomas Dodds

Perhaps a dedicated fact table that has that count already figured out - it's much more efficient.

[Deleted User]

+1 to Thomas. It's much more efficient and you have a lot more possibilities for derived calculations which will otherwise yield "wrong" results with regards to pre/post-aggregate calculations.

Also all dimensionalities around it will automatically work and aggregate.