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

[amit_saini]

Please see the attachment.

there are 3-4 sync keys , but based on requirement those are mandatory.

But please suggest if there is some better way of doing this.

Thanks,

AS

[avinashelite]

currently I don't have the QV license ...can you please share the snapshot of your data model ?? you can get this in the table viewer

[amit_saini]

[avinashelite]

For tables

ISEG: Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key

and rename the keys like Doc_Num as ISEG_Doc_Num for all the four keys

similary  for the table

MSEG: Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key

and rename the keys like Doc_Num as MSEG_Doc_Num for all the four keys

Then build a link table as below

LINK_TABLE:

LOAD Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key,

Doc_Num..&'-'&MJAHR as MKPF_Key,

Material_.. as Mater_Material_..

Resident

ISEG;

LOAD Build a custom key out of Doc_Num..&'-'&MJAHR&'-'&Material_..&'-'&ZEILE as Master_Key,

Doc_Num..&'-'&MJAHR as MKPF_Key,

Material_.. as Mater_Material_..

Resident

MSEG;

In the MKPF table

MKPF:

LOAD Doc_Num..&'-'&MJAHR as MKPF_Key,

Doc_Num.. as MKPF_Doc_Num..,

MJAHR as MKPF_MJAHR,

other fields

In the MARA table

MARA:

LOAD

Material_.. as Mater_Material_..,

MTART

[amit_saini]

Hi Avinash,

Thank u so much for all ur suggestions ,actually similar conept + building a fact table I have tried already , but this is not giving me correct out based on requirement , later contacted SAP specialist who helped be with linking and finally we are getting right output , even though we ate having SYC keys , now just to add excel sheet data to this datamodel , but this is everytime making circular loop!

Let me try ur suggestion one more time , I appreciate all ur help!

Thanks,

AS

[avinashelite]

Please try with my suggestion it should work .....and don't forget to mark the helpful and correct answers

[amit_saini]

Sure

[amit_saini]

Hi Avinash,

I think this will work , but I'm getting 2 error's , I hope my understanding regarding script was fine.

Please see the attachment.

Thanks,

AS

[avinashelite]

Replace your link code with the following code this should work

LINK_TABLE:

Load

//MBLNR&'-'&MJAHR&'-'&right([MATNR],10)&'-'&ZEILE as Master_Key,

Master_Key,

ISEG_Doc_Num_Key&'-'&MJAHR as MKPF_Key,

//MBLNR&'-'&MJAHR as MKPF_Key,----------------------(Here Getting error)

//right([MATNR],10) as Master_Material

ISEG_Material_MATNR as Master_Material

Resident

ISEG;

Load

//MBLNR&'-'&MJAHR&'-'&right([MATNR],10)&'-'&ZEILE as Master_Key,

Master_Key,

//MBLNR&'-'&MJAHR as MKPF_Key,----------------------(Here Getting error)

MSEG_Doc_Num_Key&'-'&MSEG_MJAHR as MKPF_Key,

//right([MATNR],10) as Master_Material

MSEG_Material_MATNR as Master_Material,

Resident

MSEG;

[amit_saini]

Now the only issue is here:

MBEW:

LOAD

     right([MATNR],10) as Master_Material,

     BWKEY as Plant ,    --------(If I comment Plant than no circular loop , else we are having problem)

     BKLAS,

     LBKUM

FROM

$(vPath)MBEW.qvd

(qvd);