Match ID's from two different tables

Hello,

I got a question again :slight_smile: I try to do an OLAP table where I need to combine data from different tables.

I try to explain the case as good as I can. In my table “T052U_BSEG” I got the column named “TEXT1” (here is a description in each column) and the column “ZTERM” (here i an ID which is unique for each different description, but a description can come up multiple time).

On the other hand I got the table “PB_CLAR” where I got two columns with the ID’s from ZTERM in it. I want to create a column in an OLAP table that the ID in “PB_CLAR” look up the ID in the “T052U_BSEG” and pull the right description from “TEXT1”

Thank you guys in advance

Hi Sasan,

What kind of a table is PB_CHAR?
What’s the relationship between PB_CHAR and T052U_BSEG? Is it 1:1 or 1:n or n:n (I mean how many rows in one table correspond to how many rows in the other table)?

Would it be possible to add a column to PB_CHAR where you concatenate the two other columns to make the ID numbers, and then add a foreign key relationship into the data model between PB_CLAR that and T052U_BSEG using this new column and ZTERM? This is only possible if the tables don’t have a many to many relationships.

Once a direct relationship has been established it should be easy to make the table you want.

If it’s not that straight forward or you want more support then feel free to write back :slight_smile:. Please include a screenshot of your process model if you do.

Best wishes,

Calandra

Hello Calandra,

thank you for your message. In my understanding the relationship is 1:1, there is only 1 row corresponding with 1 other row from the tables. PB_CHAR and the others are all tables extracted from SAP. Is it possible to solve this without adding a column to PB_CHAR? I would have to ask our programmers to implement this and this would take some time.

Basically I want to do something like a VLOOKUP from excel, where i can look up a specific value and I find a specific other value for it.

Beste Wishes,
Sasan

Hi Sasan,

I have an idea of a solution you could try. I was unable to test it on a situation just like yours so I can’t guarantee it will solve your problem. But I was able to confirm that SOURCE / TARGET can combine columns from tables without direct parent child relationships.

The idea is to use SOURCE / TARGET.

Step 1: make a new OLAP table

Step 2: make a dimension with the code

SOURCE(“PB_CLAR”.“ID Column part 1” || “PB_CLAR”.“ID Column part 2”)

Step 3: make another dimension with the code

TARGET(“T052U_BSEG”.“ZTERM”)

Step 4: make a KPI with the code

TARGET(“T052U_BSEG”.“TEXT1”)

You should then be able to hide the second dimension.

Let me know if this solution works. If not, please write with a screenshot of your processes model and we’ll go back to the drawing board to try and find a solution that works for you.

Best wishes,

Calandra