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
Join the T001W table with MBEW this will solve the issue
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!
Rename the NAME1 in MBEW table ..plant and NAME1 are one and the same so no need of 2 keys
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!
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
Thanks u so much
finally you the correct data model