Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
You mean to say the output you have given is wrong?
can you clarify with final required output?
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
in expression tab Total mode is there in that check the sum instead of Expression total
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
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.
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
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
Yes Prabhu....!!!
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)