Loading Weekly Forecast Data from APO Demand Planning to S&OP via HCI

[former_member195150]

Hi all,

I am trying to load forecast data from a planning area in APO Demand Planning (DP) to a planning area in S&OP via HCI. The source planning area has weeks and months as time buckets. I have generated a data source for the planning area in APO DP and use it as source in HCI. Transferring the data on monthly level works fine. Now I have the requirement to transfer the data on weekly level. Here I have a problem where hopefully somebody can help me with.

From APO DP I get the time bucket information in the field CALWEEK in the format '<Year><Week>' e.g. '201452'. The planning area staging table on S&OP side uses a datetime field to identify the weekly time bucket as you can see in the screenshot below.

I have not found any HCI function to derive the date from a calendar week. Due to the year dependency there also seems to be no really simple logic to do this conversion. Does anyone have an idea how to reach such a data load on weekly level?

Thanks and Kind Regards,

Thomas

[Irmi_Kuntze · Accepted solution]

Hi Thomas

So you are in S&OP as well now 🙂

What exactly is your requirement, to move from source = calweek to target = months?

I cannot really read your screenshot, too bad quality, sorry

In gerenal there is no functionality available to convert calweek to months or dates in HCI.

Plus there is no possibility yet to map an information from two different source systems

Our requirement was, to read data in calendar weeks and to convert into months

Source for that was a file

What we did is, that we created another file in the same folder that had missing information, e.g. in one column the calweek, in another the start date, the months etc. Whatever information you need for your requirement

If the files are in the same folder, you can merge the information, as that is considered as the same source

As your source is APO, you could e.g. create a maping table in the exact same APO-system which you than import to HCI, and than you should be able to merge the two sources in HCI in the dataflow.

Yours

Irmi

P.S.: Do you use the datasource on planning area directly and entered the name in ROOSATTR table, or you load via infocube first?

[former_member195150 · Accepted solution]

Hi Irmhild,

thanks for your reply! We are directly using a planning area DataSource in HCI (without InfoCube) and have entered a generic entry OLTPSOURCE = '9A$COMPLETE' in table "ROOSATTR" to release all DataSources in the 9A* namespace.

Although we use weekly buckets in our planning area on both sides, we need to derive a date from the calendar week for the mapping in HCI. To my understanding this is a consequence of the S&OP design regarding time profiles. I understand that there is no functionality available on HCI for such a conversion, hence we probably need to derive the date from the week in the source system already. There is for example an ABAP function available that can do this. We have not been using files so we have to think about the best way to reach that.

Kind Regards,

Thomas

[Irmi_Kuntze · Accepted solution]

Hi Thomas

just to clarify: We had two different sources, the one with the real data, and the one with the "translation table" looking like that:

YEAR_WEEK	CAL_DATE	WEEKDAY	WEEK	YEAR
2010-01	2010-01-01	Fr	1	2010
2010-01	2010-01-02	Sa	1	2010
2010-01	2010-01-03	So	1	2010
2010-02	2010-01-04	Mo	2	2010
2010-02	2010-01-05	Di	2	2010
2010-02	2010-01-06	Mi	2	2010
2010-02	2010-01-07	Do	2	2010

The Excel had some formula, so we just created it for the next possible years ...

And only in HCI we merged the two, which was possible because they where stored in the same folder

That means you do not need to enhance your datasource , it would be enough to have a (or more) table with that kind of information, such as standard tables for public holidays etc, or you create a z-table and provide any info you need, e.g. if you have a week that lies in 2 months and you would need to put value to the month with more days in the week

As far as I know, you can combine with function modules / bapis as well, but dont know how

[Former Member]

Since I have seen this and similar questions several times now, I thought it makes sense to try it our and explain how to do this. Calculating the date based on the week info is possible in HCI, it's just a matter of math and using some date time functions.

First we need to make sure we have the right definition of "week". ISO standard (and also what you see in Outlook) is that the first week of the year, is the week that contains 4/Jan.

I also assume that in order to translate week to date, you want to set the date to the Monday of that week.

And last assumption is that we read the week as a numeric value (not a string) in the format YYYYWW.

With the above assumptions, this is the correct formula in HCI to caclulate a date from the week number:

to_date(

   to_char(

      floor( Weeks.Week/100),

      '0000')

   || '-01-04', 'yyyy-mm-dd')

- day_in_week( to_date(

   to_char(

      floor( Weeks.Week/100),

      '0000')

   || '-01-04', 'yyyy-mm-dd')   ) + 1

+ 7 * ( mod(Weeks.Week,100)-1)

Basically what this formula does is construct the YYYY-01-04 date based on the year from the week number. Next find out which day in the week this is and substruct days to make it a monday.

Finally add 7 * weeknumber to get the monday in the correct week.

I ran my task in HCI with some sample dates and get the correct results (see below):

Week,Date

201301,2012.12.31

201401,2013.12.30

201408,2014.02.17

201430,2014.07.21

201501,2014.12.29

201601,2016.01.04

201701,2017.01.02

You can check the task itself in http://sandbox.integration.ondemand.com/DSoD/session/logon/LEARN_HCI in the project TEST_Ben, task Test_Week_To_date.

Ben.

[former_member288194]

Hi everyone,

I need to perform the same kind of conversion, but my input is a VARCHAR in this format:

YYYY-WW.

Based on the formula proposed by Ben, I tried to calculate the date with the following formula:

to_date( substr( Week.Week, 1, 4) || '-01-04', 'yyyy-mm-dd' )

- day_in_week( to_date( substr( Week.Week, 1, 4) || '-01-04', 'yyyy-mm-dd' ) )

+ 1+ 7 * cast( substr( Week.Week, 6, 2), 'int')

The first line works fine.

The second gives me this error:

Expression datatype <INTEGER> cannot be converted to expected datatype <TIMESTAMP>

The third gives me this error:

internal errorString index out of range: -1

I’ve tried many other ways of writing these lines, but I cannot make them work…

(I have just started to work on HCI for S&OP)

Can someone help me with my issue?

Thanks a lot,

Pierre

[Former Member]

Hi Pierre,

what did you do to know that the second line gives this error? Executing the second line alone will not work as far as I understand HCI.

I guess that your target field is of data type <TIMESTAMP>. The second line alone is of type <INTEGER>. Assigning and <INTEGER> to a <TIMESTAMP> probably does not work and gives you the error message you mention.

However, executing the first line together with the second line should work. The first line is of type <DATE> and HCI can add/subtract integers to/from dates. The result is of type <DATE> and this can be converted in <TIMESTAMP>.

For the third line I'm clueless and unfortunately I have no HCI available at the moment to do a quick test.

Regards,

Volker

[former_member288194]

Hi Volker,

Thank you for these elements!

Indeed, I tried to execute the second line alone but executing the two first lines together works fine.

For my third line, I still have no clue.

I have tried to replace it by something really simple:

+ cast( '02', 'int' )

But I get the same error internal errorString index out of range: -1

Anyone?

Thanks,

Pierre