Counting matched items based on several infoobjects

[Loed]

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

[former_member28818]

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

[Loed]

Hi guys,

Any other ideas about this scenario?

Thank you.

Loed

[Loed]

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

[former_member186445]

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

[Loed]

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

[former_member186445]

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

[Loed]

Hi,

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

Thanks.

Loed

[former_member186338]

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

[Loed]

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