Skip to Content
0

Counting matched items based on several infoobjects

Aug 07, 2017 at 02:30 PM

88

avatar image

Hi guys,

Just want to ask some ideas on how you are going to solve the issue below.

So I have the sample data above.

I wanted to count the matched items of company A compared to company B based on REFERENCE and AMOUNT.

Since the AMOUNT and REFERENCE numbers 500, 600, and 800 are both available in BPC company code A and B, they will be counted for MATCHED items. Then the remaining reference numbers 300, 400, 700, and 900 will be under the NOT MATCHED items. Even though REFERENCE 900 is both found in company A and B, it is still under the not matched items because the amount is different from each other.

This will be my final report.

Is there a way to solve this in query designer? Or any other idea on how to approach this kind of scenario?

NOTE:

The user will choose which BPC company code will be the reference, in this case COMPANY A was the reference company code so the count of not matched items were counted based on the values found in COMPANY A. If company B was the main reference, the not matched items should be 3 for 900, 1000, and 1100.

EDIT:

After counting the MATCHED and NOT MATCHED items, I also need to show the list of REFERENCE NUMBERS that are matched and not matched.

Something like this, can be 1 or 2 queries.

Thanks a lot.

Loed

delete.jpg (65.6 kB)
delete.jpg (12.8 kB)
delete.jpg (20.7 kB)
delete.jpg (24.4 kB)
10 |10000 characters needed characters left characters exceeded

Removed secondary tag: SAP Knowledge Acceleration for SAP Business Planning and Consolidation | SAP Knowledge Acceleration for SAP Business Planning and Consolidation, Edge edition

0
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Vadim Kalinin Aug 08, 2017 at 07:54 AM
0

In Excel you can concatenate all fields to match in a single string and use VLOOKUP...

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi,

I already have an idea using purely excel functions. I'm just wondering if this can be achieved in a query or in the transformation level since the solution using excel workbook has poor performance because of so many formulas.

Thanks.

Loed

0
Mario Tibollo Aug 08, 2017 at 09:57 AM
0

are you comparing exactly 2 company codes or does the solution have to be valid for N company codes?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi,

The actual requirement is N company codes, depending on the number of company codes the user entered in the variable.

Thanks.

Loed

0
Loed Despuig Aug 09, 2017 at 01:23 AM
0

Hi guys,

Edited the question above. Maybe this can help.

EDIT:

After counting the MATCHED and NOT MATCHED items, I also need to show the list of REFERENCE NUMBERS that are matched and not matched.

Something like this, can be 1 or 2 queries.

Thank you.

Loed

Show 2 Share
10 |10000 characters needed characters left characters exceeded

are you comparing exactly 2 company codes or does the solution have to be valid for N company codes?

0

Hi,

Solution for 2 company codes will be fine. I will just tell the users to run the queries depending on their desired company codes to be compared.

Thank you.

Loed

0
Loed Despuig Sep 11, 2017 at 12:45 AM
0

Hi guys,

Any other ideas about this scenario?

Thank you.

Loed

Share
10 |10000 characters needed characters left characters exceeded