Data Model / Script help

[amit_saini]

Hi Folks ,

Below is my scenario:

MKPF:

LOAD //MANDT,

     MBLNR as Doc_Num_Key,

     BUDAT as %DATE_Key

FROM

$(vPath)MKPF.qvd

(qvd);

----------------------------------------------------------------------------------------------------------

LET vMinDate = num(makedate(2013));

LET vMaxDate = num(today());

Datefield:

LOAD

    $(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

    Datefield as %DATE_Key,

    num(Datefield) as NumDate,

    date(Datefield, 'DD.MMM YYYY') as Date,

    year(Datefield) as Year,

    month(Datefield) as Month,

    'Q'&ceil(month(Datefield)/3) as Quarter,

    dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

    dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

    dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

    dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

    day(Datefield) as Day,

    week(Datefield) as Week,

    weekday(Datefield) as Weekday,

    weekyear(Datefield) as WeekYear

RESIDENT Datefield;

-------------------------------------------------------------------------------------------------

T001W:

LOAD

     WERKS as Plant,

     NAME1

FROM

$(vPath)T001W.qvd

(qvd);

This above data is coming from SAP and this below data I need to link from excel, which looks like below:

for each plant in 'CWC','KDT','KLA','KMX','KOC','KSI','KTX','KUS'

Data:

LOAD [Local currency],

     Year

     [Week of],

     Plant as NAME1,

     [Value of material before counting],

     [Net Dollar Value of Inventory Adjustments],

     [Absolute Dollar Value of Inventory Adjustments],

     [Inventory Accuracy rate Net],

     [Inventory Accuracy rate Absolute],

     [Number of adjustments],

     FG,

     WIP,

     [FDM's],

     [Raw Material],

     Components

FROM

[.............$(plant)%20Inventory%20Accuracy.xlsx ]

(ooxml, embedded labels, table is Data, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

next

Linking should be:

"Year" and "[Week of]" from Data table need to be linked with Calendar + "Plant" from Data need to be linked "NAME1" from T001W table

I have simply tried renaming them as same name field , but this is not working , getting circular loop!

Please suggest me possible best way , so that I can link Year ,Week of  fields from excel to SAP table + Data.Plant to T001W.NAME

Thanks,

AS

[avinashelite]

Join the T001W table with MBEW this will solve the issue

[amit_saini]

Sorry

MBEW:

LOAD

     right([MATNR],10) as Master_Material,

     BWKEY as Plant ,

     BKLAS,

     LBKUM

FROM

$(vPath)MBEW.qvd

(qvd);

Join(MBEW)

T001W:

LOAD 

     WERKS as Plant,

     NAME1

FROM

$(vPath)T001W.qvd

(qvd);

still circular loop!

[avinashelite]

Rename the NAME1 in MBEW table ..plant and NAME1 are one and the same so no need of 2 keys

[amit_saini]

NAME1 will give me name of the Plant

And Plant will give me number , for example output will be Texas-5024

So need both of them!

[avinashelite · Accepted solution]

if that's the case then create a mapping table and import the data in both the tables...and don't forget to rename in both i.e both should have different name like MBEW_NAME1 etc

[amit_saini]

Thanks u so much

[avinashelite]

finally you the correct data model