Distinct Help !!

[dhanu_today]

Hi Everyone,

I need some help to get the below requirement.The data and out put as below.

Data:

Region	Cust_id
West1	ABC
West1	CBC
West1	BCA
North1	BCA
North1	BZC
South1	ABC
South1	BBB
South1	BBC

The output is like;

Region(Dimension)	Count(distinct Cust_id)
	6
West1	3
North1	2
South1	3

Req:

The 6 is expression total in staright table.When we make the sum its showing the count as 8.But the requirement is we have to show the distinct cust_id's region wise.To do this we have to consider only count as 1 for the cust_ids available in multiple regions.E.g.BCA is available in both West1 and North1 so then we have to consider the count as 1 at any one of the region.The same logic is for ABC as well.I have to do the same in design level is an added advantage.

Let me know if in case of clarity required.

Thanks in adv,

Dhanu

[MK_QSL]

You mean to say the output you have given is wrong?

can you clarify with final required output?

[dhanu_today]

Thanks for Quick Reply...The out put is like below.

FORMAT1:

Region(Dimension)	Count(distinct Cust_id)
	6
West1	3
North1	1
South1	2

3:ABC,CBA,BCA,

1:BZC

2:BBB,BBC

FORMAT2:

Region(Dimension)	Count(distinct Cust_id)
	6
West1	1
North1	2
South1	3

1:CBC

2:BCA,BZC

3:ABC,BBB,BBC

The required output should be any one of the format.As per the req: mentioned above explanation.

Regards,

Dhanu

[nizamsha]

in expression tab Total mode is there in that check the sum instead of Expression total

[dhanu_today]

Thanks Nizam...When am checking the sum its giving 8 which is not the required value.

I need to get the  value as 6 when am checking the sum option.

Reagrds,

Dhanu

[Anonymous]

Dhanu,

You may have to re-asses your requirement and output formats you have given.

In format-1 the table shows count is 3 for West1 and 1 in format-2.  Can you please explain what is the business rule for this calculation?  Once you have your business rules correct then you can build the application. 

From the example you have given, my understanding is that the customer is corporate customer and having operations in multiple regions.  Therefore the customer is to be identified with one region only.  In such a scenario you need to have your data with a flag identifying the customer with the primary region.  You will then be able achieve the results you are looking for.  Example is given below.

[dhanu_today]

Thanks Sudheer...The business rule to get the mentioned value is we have to count the distinct custid's region wise.

Out of 2 formats we have to show any one of the format.The formats results are based on Distinct custid's.

Say in format1;

West1--3---ABC,CBA,BCA

North1-1--BZC(Here actually North1 is having BCA and BZC.But the BCA custid has already considered in West1 so am not considering in North1)

South1--2--BBB,BBC(Here actually South1is having BBB,BBC,ABC.But the ABC custid has already considered in West1 so am not considering in South1)

Same type of logic i have applied in format2 also.

I hope you get clear idea.

Thanks ,

Dhanu

[#8]

Hi,

Total 6 is expected and that is what you get right?

BCA is available in two region and you want to display only one region in the chart?

Is that what you are expecting?

Thanks,

Prabhu

[dhanu_today]

Yes Prabhu....!!!

[MK_QSL · Accepted solution]

Use below script...

=================================

Temp:

Load * Inline

[

  Region, Cust_id

  West1, ABC

  West1, CBC

  West1, BCA

  North1, BCA

  North1, BZC

  South1, ABC

  South1, BBB

  South1, BBC

];

NoConcatenate

FINAL:

Load

  Region,

  Cust_id,

  if(Cust_id = Previous(Cust_id), 0, 1) as Flag

Resident Temp

Order By Cust_id, Region;

Drop Table Temp;

===========================

Create a straight table

Dimension

Region

Expression

SUM(Flag)