Mimmicking a Non-equi-join

[Former Member]

I'm trying to do something that sounds simple in theory but not sure how to implement in BW. Basicly I have 1) A Material Master that contains all of our Materials and 2) A Bill of Material ODS that contains all BOMS. I want a list of all Materials that are NOT found in the Bill of Material ODS.

I thought maybe I could create an InfoSet that combines the BOM ODS with the 0Material InfoObject but I can't find a setting for non-equi-join. I can find inner-join and left-outer-join but not a non-equi-join. Any ideas on how to go about this??

Thanks

[Former Member]

Patrick, Interesting scenario!!!

Havent implemented something like this but very much feel the need in the future. Coming to the Infoset with ODS which is basically a join where you have data which is a kinda interection. You probably wont have any data doing this way. You probably need to impose a condition which says Materias not in BOMS ODS on the any of the keyfigures as already suggested by Ashish.

[Former Member]

I think that's the part I'm missing Pradeep... I'm not really a query expert so I'm not sure how to 'impose a condition on the key figure'. I know how to put a 'restrict' on a particular characteristic but not a 'condition' on a key figure... what am I missing?

Thanks!

[Former Member]

I can't seem to figure this out... I tried both an INNER JOIN and a LEFT OUTER JOIN and they both end up with the same number of records. In either scenario it only shows the materials that exist in the ODS.

[Former Member]

Hi Patrick,

Another Option will be to create a multiprovider over the ods and the infoobject..

Then use a condition in the query to display all materials for which KF value =0.

Hope you will get it this time..

Let me know

Ashish

[Former Member]

Ok I have had a query developer explain to me how to add a 'condition' to the query. I have tried adding a condition to the query both from an INFOSET and from a MULTIPROVIDER and neither way is working. The are both returning zero results. When my condition is EQUAL 0 then I get no records. When my condition is NOT EQUAL to 0 I get all the records that match. It doesn't seem to work. Any other suggestions?

[Former Member]

The more I think about this the more I think it will not work. For example when looking at RSISET I can see the data and when I search for a material that I know does not exist on any BOM (only exists in 0material) it will not appear. The fact is that there are 3537 different materials in 0Material yet only 1469 in the BOM ODS. No matter how I do it there is always 1469 records in my query resultset. It simply doesn't seem to do a non-equi-join, it only pulls in the matches. So if the multiprovider only contains the matches then the condition in the query can not possible pull the records magically from thin air. This is just my thought, can you prove me wrong?

Now I'm wondering if I should approach from a different angle; somehow create a new custom ODS that loads all of the materials from 0Material. In my new ODS I could have an infoobject that will simply contain the value YES or NO. In the update rules I could have a lookup that goes to the BOM ODS to try to find a match; if a match is found then it puts YES and if not it puts NO. Then I can easily create a query from this and filter out the YES values. What do you guys think?

[Former Member]

Hi Patrick,

Try this..

1. Create a multiprovider between the ODDS and the Info Object.

2. In the Identification tab of the Multiprovider, make sure that you select 0MATERIAL from both ODS and InfoObject.

3.Now when you pull in Material in your query, by using preper restrictions based on the Keyfigures in the ODS and other attributes of Material which are not present in the ODS you should be able to get all the materials not present in the ODS.

You might have to do some trial and error to fit this logic to your scenario..

As far as its working is concerned..I have implemented this and it works fine in our production queries..

Good Luck

Ashish

[Former Member]

Hi guys, Its been very busy since morning. I couldnt get back to you earlier. So Patrick you still seem to stuck with the same situation...haan!

I was just goin through your discussion and i have done something similar to what Ashish has suggested and with the Multiprovider and it should work as it will pull all the data which has 0Material. Then by a little logic you can separate the two sets of data. Hope it works out guys. All the best.

Pete

[Former Member]

You can use the left outer join to make the infoset.

While using it in the query there won`t be any records for the Materials not present in the ODS.

So just puling the Material Info Object in the query and then putting a condition on one of thee Key figures present in the ODS you wil be able to get a list of Materials not present in the ODS.

Ashish

[Former Member]

Ok I just tried this and it seemed to do the opposite. The query gave me all materials that WERE found on the BOM.

Here's what I did;

1) I created an Infoset and choose the BOM ODS

2) I then inserted the 0Material InfoObject into the Infoset

3) I connected the two by dragging a line from 0Material to 0Material. I right click on the line and choose LEFT-OUTER JOIN.

4) I saved and activated

5) I created a query and choose simply 0Material

6) It is showing me all materials that are foundin the BOM ODS.

[Former Member]

There should be 2 0MATERIAL Info Objects in your Infoset..

Make sure you chose the one from the Info Object, not from the ODS..

Also go into RSISET and look at your infoset data..

It will give you a fair Idea if your Left outer join is working and you got alll the material numbers from 0MATERIAL into the Infoset..

Let me know..This surely works..

Ashish.