topic Re: Distinct Help !! in QlikView

Distinct Help !!

dhanu_today — Fri, 28 Mar 2014 07:03:22 GMT

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

Re: Distinct Help !!

MK_QSL — Fri, 28 Mar 2014 07:11:48 GMT

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

can you clarify with final required output?

Re: Distinct Help !!

dhanu_today — Fri, 28 Mar 2014 07:23:34 GMT

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

Re: Distinct Help !!

nizamsha — Fri, 28 Mar 2014 07:23:40 GMT

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

Re: Distinct Help !!

dhanu_today — Fri, 28 Mar 2014 07:32:24 GMT

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

Re: Distinct Help !!

Anonymous — Fri, 28 Mar 2014 09:11:16 GMT

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.

Re: Distinct Help !!

dhanu_today — Fri, 28 Mar 2014 09:38:46 GMT

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

Re: Distinct Help !!

Fri, 28 Mar 2014 09:57:50 GMT

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

Re: Distinct Help !!

dhanu_today — Fri, 28 Mar 2014 09:59:13 GMT

Yes Prabhu....!!!

Re: Distinct Help !!

MK_QSL — Fri, 28 Mar 2014 10:05:13 GMT

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)

Re: Distinct Help !!

dhanu_today — Sat, 29 Mar 2014 05:25:12 GMT

Thanks Manish.It seems working in backend and is any chance to apply the same in design level because of the Region and cust_id fields are coming from diff tables with the combination big data model.

Dhanu

Re: Distinct Help !!

MK_QSL — Sat, 29 Mar 2014 06:33:34 GMT

Will try and come back to you on this