Unique count in Bex query

[Former Member]

Hi All,

I have requirement in which I need to get the count of unique combination of two fields. See the example below, based on unique Run date and ID combination I need to calculate the count.

Run Date	ID	Count
01.05.2013	A	1
01.05.2013	A	1
01.05.2013	A	1
06.05.2013	A	2
06.05.2013	A	2
06.05.2013	A	2
06.05.2013	A	2
07.05.2013	B	3
08.05.2013	C	4
10.05.2013	A	5
10.05.2013	A	5
10.05.2013	A	5
Total	5

Can you please explain on how to achieve this.

Thanks in advance,

Ramesh

[Loed · Accepted solution]

Hi Pagidala,

You need to concatenate the RUN DATE and ID first before you can achieve this in Query Designer level..

Here are the steps:

1.Create an infoobject that will be used to concatenate the RUN DATE and ID (let's name it CONC_RUN_ID)

2.Insert CONC_RUN_ID to your provider

3.Create a field routine for the CONC_RUN_ID so as RUN DATE and ID will be concatenated (post here if you don't know how to do the field routine)

QUERY DESIGNER LEVEL

4.Create a Global CKF, let's name it GL_CKF

5.Create a formula variable for CONC_RUN_ID..In the replacement path tab, choose INFOOBJECT, Attribute, then Constant 1..Let's name it FV_COUNT

6.In your GL_CKF. the formula should be:

FV_COUNT * 1

7.Create a Formula in the KeyFigure structure of your query, insert the GL_CKF..Let's name it FORMULA_KF

Regards,

Loed

[former_member182470]

Hi Ramesh,

Your requirement cannot be achieved directly in BEx. You are looking for Counter but not Count.

You have to include a new infoobject(Counter) in your Infoprovider and populate data according to the unique combination.

You may write an end routine to incorporate the logic.

• Take all data into an internal table IT
• Loop at IT into a work area and update Counter as 1
• If second record is same as first record you should update same 1 and so on....Precisely you should use multiple workareas to compare each record with the previous record
• Clear the work area(make it empty before reading second record)
• Move all the right data(records) with all three columns into other internal table(dummy)
• Finally modify the original IT with above dummy IT

Explain this logic to an ABAPer, he can easily write it.

Regards,

Suman

[Former Member]

This message was moderated.

[Former Member]

This is for a single characteristic, how can we implement for both? Any points...

[Former Member]

Hi Ramesh,

Try with exception aggregation.

First create one E1 exception aggregation as count with reference characteristic as run date.Now create another exception aggregation on E1 as count with reference characteristics as ID.

Try it and let me know if it working.

Thanks,

Malathi