Skip to Content

Outjoin issue or Context issues?

OUTER JOIN_QUESTION TO SAP

Have a circumstance where the relationship with the data is causing a problem. Meaning that there is an id in the data that is what joins the data to each other. Specifically there is a relationship between PARENT EVENT ID AND AN EVENT ID. The hierarchy is such that you have something like the following:

Form

1 Event ID 1 (Parent Event ID)

Section

Event ID (2) , (1) Parent Event ID 3, 1

Control

4,2 5,2 6, 2 7,3 8,3

For a row which has no parent the event id and the parent event id will be the same. For a child row the parent event id will equal the event id on the parent row.

Now I’ve done this in SQL but can’t put this together with Web Intelligence. I’ve tried to use multiple queries, outer joins, detail objects and have not been able to get the values into one table.

Below is the outcome of the three queries that are strung together. Using the hierarchy shown above.

The cold values above (258685677;258685731) are for “Clinical Nutrition Grid” and “Intake Nutritional Grid”.

Notice below that I’m running the second query from the first based on the hierarchy shown above.

The clinical code above (112146587;1681809363) are for “Malnutrition” and “Underweight”

Again below, notice that the level of the hierarchy is again dropped.

Code values above (258685659;258685665) are for “Evidenced By” and “Related To”.

The outcome I would like is similar to the following.

I want the data such as:

Underweight associated with the related to and evidenced by columns.

Ultimately at the very least, I want malnutrition and underweight to be broght into the bottom table!

One of my assumptions is that I could do this with a detail object but I may not be getting the context correct?

ngqza.jpeg (43.1 kB)
e1bgj.jpeg (15.7 kB)
ffkvp.jpeg (15.3 kB)
agmfr.jpeg (18.2 kB)
sc7ux.jpeg (13.5 kB)
ytsiy.jpeg (11.4 kB)
knil0.jpeg (19.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 27, 2017 at 07:38 PM

    The web form is not keeping the layout above to make the Form, Section, Control diagram make sense.

    Event ID 1 Parent ID 1

    Event ID 2 Parent ID 1 Event ID 3 Parent ID 1

    Event ID 4 Parent ID 2, Event ID 5 Parent ID 2, Event ID 6 Parent ID 2, Event ID 7 Parent ID 3, Event 8 Parent ID 3

    I hope this helps.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 27, 2017 at 07:57 PM

    What is the issue if you will create one webi query and include all three query filters in the single query.after that create three different tables at report level and apply your query filters in individual table.

    Add comment
    10|10000 characters needed characters exceeded

    • I attempted to do this a different way, assuming that my approach wasn't clear. I opened another question called "

      How to attain when there is no heirarchy". I hope that makes more sense. Sorry but since it can be done with regualar sql using outer joins, it's got to be possible in webi. Thanks !!!!

  • May 03, 2017 at 10:04 AM

    sap.jpg

    Here is a screen shot of the queries

    Add comment
    10|10000 characters needed characters exceeded