Dear Colleagues,
In my system I have 2 tables which are extracted to Celonis. From these tables I need to create objects with proper relationships.
The first one contains sales documents and has a structure:
| Sales Doc | Product ID | Sales date | Attribute |
| Doc1 | ID1 | 2020-02-01 | x |
| Doc2 | ID1 | 2026-03-01 | x |
| Doc3 | ID2 | 2026-05-01 | x |
The second one contains a cost of a product and has a structure:
| Product ID | Cost | Valid From | Valid To | Attribute |
| ID1 | 10 | 2020-01-01 | 2025-12-31 | x |
| ID1 | 12 | 2026-01-01 | 9999-99-99 | x |
| ID2 | 20 | 2026-01-01 | 2026-06-30 | x |
| ID2 | 21 | 2026-07-01 | 9999-99-99 | x |
For each Sales Doc and Product ID from the first table I need to assigned the cost based on [Product ID] and [Sales date] within the cost [Valid From] and [Valid To].
In the second table, my unique key is concatenation of the fields: [Product ID] and [Valid From] and [Valid To].
Question:
How to link these two tables?
A JOIN in the SQL transformation which would merge the both tables - is it the only way to do it?
Is it anyhow possible to keep these tables as separate objects and create any relationship which would recognize also time validity?
Thanks for the support,
Malgorzata