How to Join Two ODSs

[Former Member]

Dear All,

I have Two ODSs .

ODS1 is for Customer related Transaction records and

ODS2 is for Customer Agreement which contains Customer rate card with time validity.

ODS1 Records -

<b>Customer material Hitdate Quantity

C1 M1 20051015 5</b>

ODS2 Records -

<b>Customer material rate validfrom validto

C1 M1 10 20051001 20051031

C1 M1 20 20051101 20051130</b>

On Infoset Query where ODS1 and ODS2 (Joins on Customer and Material) shows for every transaction two records from ODS2.

Please guide me How can I restrict the time validity of ODS2 on HitDate of ODS1.

In above given eg.

Only one record should come as Query outout because ODS1-HITDATE lie in ODS2-validity interval of October

which is

Customer masterial Hitdate rate quantity

C1 M1 20051015 10 5

At Infost level if I join ODS1-Hidate with ODS2-Validfrom then Query doesnot show any value becuse it looks for Value match where it finds Hitdate value is not equal to Validfrom value.

waiting for solution

Vishal

[Former Member]

Hi Vishal,

In the query designer

1) Create 3 Formula variables respectively on

'validfrom','validto'

and 'Hitdate '

2) Use the above Formula variables in 3 Columns(formulas) of the query respectively .And then hide them

3) Define one more formula(column) as follow :

Formula A: (Column created in second step for 'validfrom')- (Column created in second step for 'Hitdate ')<=0 and

(Column created in second step for 'validto')- (Column created in second tep for 'Hitdate ')>=0

And hide them.

4) Create a condition like

Formula A = 1

Hope this will help.

Bye,

Amruth

[Former Member]

Hi Vishal,

I believe this is a data modeling issue, I guess. If I have a situation something like this, what I would is: First, I will take the ODS1 data to the next level which means into another ODS. While I am taking the data into another ODS I will write some ABAP code in update rules. The next level ODS contains the field for Rate. So, you wite ABAP code in update rules to populate the field Rate by looking up the fields of ODS2 'validto' and 'validfrom'. Hope this will help.

Rao.

[former_member184494]

Vishal ,

Please refer the use of temporal joins on Infosets ...

This might help ..

Arun