InfoSet query - eliminate duplicates while calculating amounts

[Former Member]

I got InfoSet consist of FI DSO and Dunning data DSO.

FI fields in Infoset are:

AC_DOC_NUM

LINE_ITEM

AMOUNT

Dunn. fields in InfoSet are:

AC_DOC_NUM

LINE_ITEM

DUNN_NUM

DUNN_AMOUNT

ODS are joined through AC_DOC_NUM + LINE_ITEM fields.

Every combination of FI (ac_doc_num+line_item) can have several dunn_num, and as a result I get wrong (larger) amounts in InfoSet while summing up on ac_doc_num level.

Is there any possibility to get valid FI amounts on Query level?

[Former Member]

Hi,

You can use the Key figure 'Number of Records' to obtain the correct result.

This keyfigure would be available in the query created on infoset.

For Eg:

FI Fields

AC_DOC_NUM LINE_ITEM AMOUNT

A1 10 10

A2 20 5

DUNN Fields:

AC_DOC_NUM LINE_ITEM DUNN_NUM DUNN_AMOUNT

A1 10 1 10

A2 20 1 20

A1 10 2 30

A2 20 2 40

InfoSet Fields:

AC_DOC_NUM LINE_ITEM DUNN_NUM DUNN_AMOUNT AMOUNT Number of Records

A1 10 1 10 10 1

A2 20 1 20 5 1

A1 10 2 30 10 1

A2 20 2 40 5 1

You can divide the FI amounts by Number of Records to obtain the correct result.

For the above example,

A1 FI amount = 20, but when you divide by number of records (i.e, 2 for A1) you will get the correct result as 10.

Hope this helps!

[Former Member]

now look that we get:

total FI amount = 10 + 5 + 10 + 5 = 30

total number of records = 1 + 1 +1 + 1 = 4

Grand Total Amount = Total Amount / Number of records = 30 / 4 = 7,5

Though it must be 15

[Former Member]

Hello Mr.Gediminas Berzanskis,

I am Narendra, searching for the solution for the same issue. found that you got a solution for the issue.

Here i am connecting 3 DSO's from Sales. Here I need to have the data for a scheduling aggrement where there is a shipping done in a month, the problem is that I am getting the data for all the schesduling aggrements which were happend once in a month, if for the same aggrement number if there 2 or 3 days then i am getting the average of all

example:Data in DSO level

Scheduling no. Date Value

1200000001 24.10.2009 20

1200000001 27.10.2009 10

1200000045 29.10.2009 50

1200000128 03.10.2009 40

for the above i am getting the duplicate records i.e. same value getting repeted for "n" times then i am calculating the values in reporting by dividing the value with row count. i am able to get the correct value, but if the case of above getting the average

Data in Infoset level

Scheduling no: 1200000001

Infoset values 202020202010101010+10 = 150

in report i am calculting like this 150/10 = 15

where as the exact value for the above is 30

Scheduling no:1200000045

Infoset values 5050505050 = 250

in report i am calculting like this 250/5 = 50 which is correct

Scheduling no:1200000128

Infoset values 4040404040 = 200

in report i am calculting like this 200/5 = 40 which is correct

Requesting you to help me over this, that will be very kind of you.

Thanks in advance

Narendra