Help required for time Conversion

[#1]

Hi Folks,

Got stuck where my datasource is BODS based & am I getting a timestamp(CHAR30) fields, entries as shown below in PSA

Now in the DSO , I have field TIMESTAMP(refrence of 0TCTTIMSTMP) which is of TYPE NUMC14, But standard trasformation here failes to work as I an having data is target DS column as say 09.30 00:00:00( say for 1st records) & like wise.  though I can handle this puttinf some routine but I am not allowed to do any data modification atleast to 1st layer( Data propagation layer in LSA++) .

Can any one suggest, is there any way, I can handle this via standard transformation.

Also I am facing a strange issue wherein wherein Whereever I receiving any timestamp field from my ECC source its comversion to time stamp has added 2 extra hpurs to it, When I checked at the InfoObject level, I saw a TIMES conversion is applied which is actually creating a issue, screeshot below for reference

As you can see above the input is 30.09.2015 10:20:00 but when in BI it turns as 30.09.2015 12:20:00(added 2 hours extra), similarly lets say an other example

In above the input is 30.09.2015 23:00:00 but when in BI it turns as 01.10.2015 01:00:00(added 2 hours extra, since its already 23 hours in inputs & hence it chnages the next day in output),

Can anyone help on above 2 issues please,

Regards,

Fox

[Former Member]

The incoming data is in CHAR format, and it has spaces and colons and dots (periods), which are not allowed in NUMC fields.

One possible approach: open up the Datasource in change mode (delete PSA contents first) and go to the Fields tab. Locate the entry for the timestamp field and give it the same data type (NUMC, length 14) as the InfoObject.

In the same entry there will be a dropdown having the options "Internal" and "External". Select External here.

Next to the dropdown there will also be a box where you can enter the conversion exit. Here, you enter the exit as TIMES.

As this is a change to the DS, you'll have to reactivate the transformation and DTP afterwards. Once you load data into the PSA after making this change, the PSA itself should contain the timestamp in NUMC format.

Message was edited by: Suhas Karnik

[Former Member]

Hi Suhas,

Thanks for your suggestion. I fairly agree th eoption you suggest but the issue is BODS Daatsources is owned by the client only we are not allowed to make chnages to it.

But i will try discussing the option proposed if that helps. But can we any laternate way of handling it in BI modelling instead of changing it at the source level.

Best Regards,

Fox

[Former Member]

Try to see if the DS is modifiable in BW.

There are alternate ways of handling it

• Write a routine in the Acq transformation to convert the incoming CHAR to a NUMC. It's not normally allowed to write routines in Acq transformations, but this is a case where an exception should be made. You're not changing the actual contents of the field or applying business logic, you're just converting it into a format that BW can use. This is the simpler option.
• Otherwise, change the acq DSO and use a CHAR field to store the timestamp. Then, in the transformation from the Acq to the Prop DSO, convert the CHAR timestamp into a NUMC timestamp

[Former Member]

Hi Suhas,

Thnaks for the prompt reply, but lets say I decide to go for conversion of CHAR(30) to NUMC(14), By this you mean you mean replacing occurances of space , ':' & '.' then making the input format as yyyymmddhhmmss & then assign it to NUMC(since NUMC will not allow activation with thse colons, period & spaces).

Or you refer to have any standard way of doing the conversion. Also once yyyymmddhhmmss is assigned to NUMC 14 which is refernce of TIMESTAMP in BI , will I able to achieve standard time conversion over it, means doing calday/calweek/calyar/calquarter calculation, time calculation etc.

Please suggest.

Regards,

Pankaj

[Former Member]

I don't think you have a standard way to do this, because this YYYY.MM.DD HH:MM:SS isn't a standard format that SAP supports.

Come to think of it, the original answer I gave (TIMES conv exit in the DS) may not work too for the same reason.

So the only option is to code it yourself. Fortunately, the code itself isn't that difficult. Here's a simple piece of code which you can adapt for your requirement.

data: ctime TYPE c LENGTH 30,
        stamp TYPE n LENGTH 14 .

 ctime = '2015.09.30 15:30:12'.

 CONCATENATE ctime(4)
             ctime+5(2)
             ctime+8(2)
             ctime+11(2)
             ctime+14(2)
             ctime+17(2)
into stamp.

WRITE stamp.          // 20150930153012     ==> YYYYMMDDHHMMSS

You should be able to convert this to CALDAY, CALYEAR and CALMONTH. Just map this NUMC14 field to those characteristics in the Acq->Prop transformation.

CALWEEK and CALQUARTER are more difficult.

So you could do this: map the timestamp to the CALDAY in the Acq->Prop transformation, then map CALDAY to all the other CAL* fields in the Prop->Cube/Reporting layer transformation. The system knows how to convert CALDAY into all of the other CAL* fields, so it will work fine.

[Former Member]

I don't think you have a standard way to do this, because this YYYY.MM.DD HH:MM:SS isn't a standard format that SAP supports.

Come to think of it, the original answer I gave (TIMES conv exit in the DS) may not work too for the same reason.

So the only option is to code it yourself. Fortunately, the code itself isn't that difficult. Here's a simple piece of code which you can adapt for your requirement.

data: ctime TYPE c LENGTH 30,
        stamp TYPE n LENGTH 14 .

ctime = '2015.09.30 15:30:12'.

CONCATENATE ctime(4)
             ctime+5(2)
             ctime+8(2)
             ctime+11(2)
             ctime+14(2)
             ctime+17(2)
into stamp.

WRITE stamp.          // 20150930153012     ==> YYYYMMDDHHMMSS

You should be able to convert this to CALDAY, CALYEAR and CALMONTH. Just map this NUMC14 field to those characteristics in the Acq->Prop transformation.

CALWEEK and CALQUARTER are more difficult.

So you could do this: map the timestamp to the CALDAY in the Acq->Prop transformation, then map CALDAY to all the other CAL* fields in the Prop->Cube/Reporting layer transformation. The system knows how to convert CALDAY into all of the other CAL* fields, so it will work fine.

[Former Member]

Hi Suhas, The time conversion in the DS won't work. Also since I am getting data from another BODS datasources wherein time stamp is of type 2015/03/31 16:00 & hence I am left with no choice then do write routine for it. Even before that need to have approval from client(but your comments on getting justification will help)

Thanks for the prompt reply.