Infoset joins issue

[Former Member]

There are 2 ODSs.

Orders ODS and Revenue ODS.

I have in Orders ODS the fields as

Doc No.     Material    Item Category         Revenue
65237        4415         IO18                  0
65237        4418         IO19                  0
65237        6789         IU01                  2000
65456        4415         IO18                  0
65456        4418         IU01                  6000
65456        6789         IU08                  4000

In Revenue ODS the fields are

Material    Item Category       Revenue  
6789         IU01                  2000
4418         IU01                  6000
6789         IU08                  4000

What should be the joins taken between the 2 ODS?

How should the relations be made?

[former_member188743]

Hello akshara20,

Please review the following notes for further guidance, they should help:

1238583 - Preliminary clarification: Information regarding BI InfoSets.

592785 InfoSets (BW): Interpretation of results

583249 InfoSets (BW): Temporal Joins

577953 - InfoSets (BW): Left outer joins

Thanks,

Diego Seben.

[Former Member]

Only when you some different fields -Char or Key figures and need to be joined to be reported in a single Query, you can use a multiprovider and need to join the Common fields.

In this case, if you report out of the first DSO , whcih has all the fields of the second DSO, you should be good.

Also if you have these two DSO's with additional fields, in ths case, Material would be the key to join both these DSO.

[Former Member]

I have some additonal fields. I just showing main fields which i need.

[Former Member]

Akshara, in the case that you have other fields,

since Material is the key ( check if you have other keys from DSO that are comman and if so you ca use them as well).

Join the material between the two DSO /Infoproivder.

Now there exists options to make it Left outer join or inner join based on:

Left outer join : if you need all records from one info provider , then make that as left outer join. -You can right click the link in material to make the selection as left outer or inner.

Inner join : if you need records that are comman in between both the infoproviders, go with Inner join.

Make sure that the check box is selected for the fields that you need to select to be available from infoset.

Thanks.

[Former Member]

Material is not the key.

the key fields are different in both ODSs.

[Former Member]

Buddy,

You don't really have an option but to write an end routine and then get those fields in one of the DSOs.

This is because- you can not join the DSOs because of no primary field available.

Cheers!!

Please award points for a helpful answer

[Former Member]

OK.

How should end routine be built?

What should be considered?

[Former Member]

Buddy,

That depends on the fields that you want to include from the other DSO.

I shall explain you with an example scenario that will help you writing the end routine and all..

This routine involves look-up on a standard DSO(OFIAR_O03) which picks up Assignment field(0ALLOC_NMBR) based on the Billing Doc Number(0BILL_NUMBER) and appends that to a Info-Cube(ZCFORM).

InfoCube- ZCFORM

Field- Billing Document Number (0BILL_NUM)

- Allocation Number (0ALLOC_NMBR)

DSO- 0FIAR_O03

Field- Billing Document Number (0DOC_NUMBER)

-Allocation Number (0ALLOC_NMBR)

Data: tmp_doc_no type /BI0/OIBILL_NUM.

data: tmp_assign type /BI0/OIALLOC_NMBR.

data: int_zcform type table of /BI0/AFIAR_O0300.

data: wa_zcform type /BI0/AFIAR_O0300.

DATA: ITAB_RESULT_PACKAGE TYPE TYT_TG_1.

Data: wa_RESULT_PACKAGE type tys_TG_1.

SELECT * FROM /BI0/AFIAR_O0300 INTO TABLE int_zcform.

tmp_doc_no = wa_RESULT_PACKAGE-BILL_NUM.

LOOP AT RESULT_PACKAGE into wa_RESULT_PACKAGE.

read table int_zcform into wa_zcform with key DOC_NUMBER = tmp_doc_no.

if sy-subrc = 0.

tmp_assign = wa_zcform-alloc_nmbr.

else.

clear wa_zcform.

clear tmp_assign.

endif.

wa_RESULT_PACKAGE-alloc_nmbr = tmp_assign.

APPEND WA_RESULT_PACKAGE TO ITAB_RESULT_PACKAGE.

endloop.

RESULT_PACKAGE[] = itab_RESULT_PACKAGE.

Cheers,

Noble

Please award points for a helpful answer..

[sathiendiran_balu]

HI ,

You already have all the fields of the Revenue ODS in the Orders ODS , then why do you need to join them ??

Regards,

Sathya