Skip to main content
Question

Relation between two tables which would include Validity

  • Nov 18, 2026 1:52 PM UTC
  • 0 replies
  • 3 views

Malgorzata Korus
Level 3
Forum|alt.badge.img+1

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