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
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
Any Suggestion???
Thanks,
AS
Hi
You can able to join the T001W and Data, because T001W doens't have any KPI.
T001W+Data become one table
link between Calendar and T001W+Data is Year.
In you case the excel file data will act as the linking table
try like this
1.first join or map the MKPF field to calendar table so you will have the one table
2. After the combining this table build a key with the year &'-'& week as Key
3. In the Excel you have the year and Weekof so build a key like Year &'-'& [Week of] as Key
4. finally link the T001W table using the Plant key which is already present in the excel
now this doesn't form the circular key
Plz help me with Script if possible!
Thanks,
AS
Try like this
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);
Join (MKPF)
Calendar:
LOAD
Datefield as %DATE_Key,
num(Datefield) as NumDate,
date(Datefield, 'DD.MMM YYYY') as Date,
year(Datefield) as Year,
year(Datefield)&'-'&week(Datefield) as Key,
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;
Data:
LOAD [Local currency],
Year&'-'&[Week of] as Key,
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))
));
-------------------------------------------------------------------------------------------------
T001W:
LOAD
WERKS as Plant,
NAME1
FROM
$(vPath)T001W.qvd
(qvd);
Sorry
Still Circular loop!
Thanks,
AS
Hi,
Tried this sol too , but same issue !
Thanks,AS
Try this but i am not sure
i think you need to drop datefield after calendar creation and store Calendar into qvd.
Sorry forgot to rename the Year
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);
Join (MKPF)
Calendar:
LOAD
Datefield as %DATE_Key,
num(Datefield) as NumDate,
date(Datefield, 'DD.MMM YYYY') as Date,
year(Datefield) as Year,
year(Datefield)&'-'&week(Datefield) as Key,
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;
drop table Datefield;
Data:
LOAD [Local currency],
Year&'-'&[Week of] as Key,
Year as Data_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))
));
-------------------------------------------------------------------------------------------------
T001W:
LOAD
WERKS as Plant,
NAME1
FROM
$(vPath)T001W.qvd
(qvd);
NOTE: do you have any other tables in the script