Data modeling

[krish2459]

Hi,

I have been confused with the below data model request.

Date model request:

1. Extract MBEW sheet
2. Extract the History data (Historic Valuation Data with suffix H, i.e. MBEWH), include only last Reporting Month and left join with MBEW
3. Create new table  from previous table and
   1.       If the Material Plant Combo has an entry in the History Table(MBEWH), then use fields from History Table(MBEWH)  else use fields from Actual Table(MBEW)

Attached is the sample data.

Please provide any ideas and suggestions.

Thanks..

[cwolf]

In both tables the period is used in a different meaning, in MBEWH it's a "valid to" period and in MBEW it's a "valid from" period

You need to convert both tables in 3 steps:
1. Concatenate both tables
2. Handle the different meaning of period by creating new fields ValidFrom and ValidTo
3. Make an interval match to get a period for each data set

Here is a sample script how do you can do this:

let vActPeriod=Floor(MonthStart(today()));
let vMinDate=num(MakeDate(Year(Today())-2,1,1)); // Load only the last three years in datamodel
let vMaxDate=Floor(MonthStart(Today()));

// Step 1 Concatenate both tables
t:
LOAD * FROM [.\MBEWH.qvd] (qvd);

Concatenate(t)
LOAD
*,
1 as Source
FROM [.\MBEW.qvd] (qvd);

// Step 2 Handle the different meaning of period
tt:
LOAD
Hash128(MATNR,BWKEY,BWTAR) as %MBEW,
*,
if(Source=1,num(MakeDate(LFGJA,LFMON,1))) as ValidFromTmp,
if(Source=1,$(vActPeriod),num(MakeDate(LFGJA,LFMON,1))) as ValidTo
Resident t;

DROP Table t;

MBEW:
LOAD
*
Where ValidTo>=$(vMinDate);
LOAD
*,
if(IsNull(ValidFromTmp),if(Previous(%MBEW)=%MBEW,AddMonths(Previous(ValidTo),1),ValidTo),ValidFromTmp) as ValidFrom
Resident tt
Order by %MBEW, ValidTo;

DROP Fields Source,ValidFromTmp;
DROP Table tt;

// Step 3 Interval match
t:
LOAD Distinct
ValidFrom,
ValidTo
Resident MBEW;

PERIODS:
LOAD
PERIOD
Where Day(PERIOD)=1;
LOAD
RecNo()+$(vMinDate)-1 as PERIOD
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

tt:
IntervalMatch(PERIOD)
LOAD
ValidFrom,
ValidTo
Resident t;

DROP Tables t,PERIODS;

Inner Join (MBEW)
LOAD Distinct * Resident tt;

DROP Fields ValidFrom,ValidTo;
DROP Table tt;

[krish2459]

Hi cwolf ,

Thanks for the reply.

Can you please expain the logic what you have post.

Seems like suggention going on other direction.

Hers is the requirement.

1. Extract MBEW sheet
2. Extract the History data (Historic Valuation Data with suffix H, i.e. MBEWH), include only Reporting Month year (Year&Month)  and left join with MBEW
3. Create new table  from previous table and
   1.       If the Material Plant Combo has an entry in the History Table(MBEWH), then use fields from History Table(MBEWH)  else use fields from Actual Table(MBEW).

Thanks..