Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Please try with my suggestion it should work .....and don't forget to mark the helpful and correct answers
Sure
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
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;
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);