Question Re: Code to correct Invalid date to correct date format. in Technology Q&A

Code to correct Invalid date to correct date format.

Former Member — Mon, 15 Jun 2015 07:08:03 GMT

Hi Experts,

I'm loading the data from datasource to DSO and then from DSO to Infocube.

While loading the data from datasource to DSO, I was getting error during DSO Activation. The error was "Activation of M records from DSO terminated" .

After changing the DSO Setting as "No SID Generation", I was able to load it into DSO.

So here is the issue m facing--

While loading from DSO to infocube, I got the error "Exception in Substep Write to Fact Table" . Then I found that this error was due to some wrong entry of date in Date column - /OBIZ/LAEDA

Some date values are empty. Some are in correct format- DD.MM.YYYY.

But some are in wrong format like Example - 20.2..15.1

So could anyone please help me in writing the ABAP Code in start routine of Transformation between DSO and Infocube.

Requesting ABAP and BW experts to help me out in successfully loading into Infocube.

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

KodandaPani_KV — Mon, 15 Jun 2015 07:19:48 GMT

Hi,

basically if you uncheck the 'No SID Generation ' it will allow to with out any error.

but if you select 'No SID Generation' it will get error.

edit the records in PSA for paritcular date column

while loading to dso to cube use the error stock DTP and correct it the error recrod.

basically system will alllow the format - YYYYMMDD but it give output - DD-MM-YYYY.

extranl format lenght - 8 and internal format lenght - 10.

Thanks,

Phani.

Re: Code to correct Invalid date to correct date format.

saurabh301 — Mon, 15 Jun 2015 08:18:30 GMT

Hi Shruti,

Since you have changed the DSO setting to no SID generation, the wrong format of date was loaded into DSO.

So I would suggest to correct the format when you load to DSO. This way you will not face any issue for DSO to CUBE load.

If you could share the value of date for which you got the earlier activation error, may be some solution can be suggested.

Thanks,

Saurabh

Re: Code to correct Invalid date to correct date format.

Former Member — Mon, 15 Jun 2015 08:44:20 GMT

Hi Phani,

Thanks for your answer.

But when I checked the ERROR STACK, its saying data records to be edited are 86.

So, I corrected one record by providing valid date and then saved it.

After that I have to create ERROR DTP. But that option is disabled.

M not able to click on Button " Create Error DTP".

So what is my doubt is -

Only after correcting all records in Error Stack , will I be able to click on "Create Error DTP" ?

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

Former Member — Mon, 15 Jun 2015 09:00:54 GMT

Hi Saurabh,

If I select "SID generation" in DSO Setting, then I will get error "Activation of M records from DSO terminated " during DSO Activation.

So I had to select "No SID Generation".

Now the issue is coming between DSO and Infocube.

Infocube is not accepting the Invalid Date.

Some dates are stored in wrong format and meaningless.

Eg- 20.2..15.1

20.1..26.1

Please guide me ..

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

former_member214415 — Mon, 15 Jun 2015 09:07:00 GMT

Hi Shruti,

Settings for error stack in dtp in update mode should be "update valid records, No reporting(Request Red)" or "update valid records, Reporting possible(Request Green)". Once you do error handling like this so erroneous record will be in error stack, after correcting save them. Then you can create error DTP. So execute it and its not like that you can create error DTP only after correcting records in error stack. Better you correct the error in date at DSO level only. So you can create ERROR DTP or correct at PSA level(error handling is - No update, No reporting). so activation of M records in DSO will not be terminated.Then load data from dso to cube.

Thanks,

Swapna Jain

Re: Code to correct Invalid date to correct date format.

saurabh301 — Mon, 15 Jun 2015 09:29:49 GMT

Hi Shruti,

Could you please display the record in DSO and let us know how the date is present in DSO and what is the expected value.

Thanks,

Saurabh

Re: Code to correct Invalid date to correct date format.

KodandaPani_KV — Mon, 15 Jun 2015 10:00:09 GMT

Hi Shruthi,

go to standard DTP -> select update tab -> click on the error stock DTP.

now it will enable error stock DTP.

then click on the PSA symbol then you wil find the error records. edit and execute the Error DTP.

before click the error stock DTP then edit it.

Thanks,

Phani.

Re: Code to correct Invalid date to correct date format.

Former Member — Tue, 16 Jun 2015 05:11:21 GMT

Hi Saurabh,

Below is the screenshot of records present in DSO Active table.

Now, while loading it into infocube, m getting error due to this wrong data entry into infocube.

So I created ERROR DTP between DSO and Infocube.

When I checked the ERROR STACK, then found there are 86 records which I need to correct it.

But doubt is how to correct the date in ERROR STACK when it is wrong in source system itself.

I have compared the date with the ones present in source system (datasource).

Both are same.

How can I guess the correct date for each 86 records.

What can I do with the meaningless date present in datasource.

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

Former Member — Tue, 16 Jun 2015 05:18:47 GMT

With this kind of junk data, any logic you write will have to guess the date values. You could write a logic that assumes that the first four characters "20.1" refer to 20th Jan or to 2011, but ultimately that's not accurate, it's just a guess of what "20.1" might mean.

And it is not a good practice to report on dates that are basically the result of guesswork done by the code.

So please get these dates corrected from the source system. In the meantime, either exclude these records using the error DTPs, or blank out the date if the user is okay with doing that. But please do not write code to fix these dates - that is a job that the code should not be doing anyway.

Re: Code to correct Invalid date to correct date format.

Former Member — Tue, 16 Jun 2015 05:34:19 GMT

Hey Suhas,

Thanks for your opinion .

If I create ERROR DTP, then I have to correct the Date by guessing which is not suggestable.

Could you please say me the code to write in start routine about how to exclude the records or how to blank out the date containing this error and meaningless date.

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

Former Member — Tue, 16 Jun 2015 05:51:43 GMT

There are two options you have:

Create Error DTP, and in the error handling select "Valid records update, request green". With this no code needs to be written, and the invalid dates will automatically be stopped by the error DTP. This is the simpler option.
The other option is to create a start routine in the transformation. Loop over the Source Package, and within the loop call the FM DATE_CHECK_PLAUSIBILITY. This FM takes a date and tells you if the date is valid or not. So within the loop, pass the date to this FM and check the validity. If the date is invalid, delete the record from the source package. Take an ABAPpers help if needed.

Re: Code to correct Invalid date to correct date format.

Former Member — Wed, 17 Jun 2015 08:50:26 GMT

Hi Suhas,

I created the ERROR DTP between DSO and Infocube.

Now all records are present in Infocube. Infocube manage screen -

On top of infocube, I have created one multiprovider. This multiprovider has only one infocube.

In multiprovider, records are present.

Then I created Query in which I have included as OCALYEAR as characteristic restriction.

Created a variable for it.

But when m executing in rsrt tcode, Its not displaying any records inspite of giving the correct value for Calendar year.

M stuck here. Please help me. What may be the reason behind it.

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

Former Member — Wed, 17 Jun 2015 08:53:53 GMT

Hi phani and Swapna,

Thanks for ur help.

Created the ERROR DTP and loaded the records in infocube.

But now one more issue m facing.

Could u please refer my reply to Suhas in which I have mentioned the issue.

Waiting for your reply.

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

Former Member — Wed, 17 Jun 2015 08:55:12 GMT

You have a red request in the cube, due to which none of the requests are available for reporting.

Also while creating the Error DTP, in the Error handling, you should give "Valid records update, request green". It looks like you have given it as request red.

So first delete both requests from the cube, modify the error handling as above, and then reload.

Re: Code to correct Invalid date to correct date format.

former_member214415 — Wed, 17 Jun 2015 09:36:39 GMT

Hi shruti

First, No data is available in multirprovider because it is a logical infoprovider which doesn't hold any data but when query is hitted, it gets data from underneath infoprovider. In your scenario, you have created multiprovider on top of infocube.

Second, Check the settings of variable which you have created on cal year and check mappings also for the calender year.

Thanks,

Swapna Jain

Re: Code to correct Invalid date to correct date format.

Former Member — Wed, 17 Jun 2015 11:10:20 GMT

Hi Shruti,

Is this data which you posted is from PRODUCTION environment?

This is JUNK data, best practice is to get the data corrected in source and then extract the same into BW info providers.

Even though you create a ERROR DTP you need to correct the entries every time you get the junk data and then make them available for reporting. Which is not a good practice.

Regards

Re: Code to correct Invalid date to correct date format.

Former Member — Thu, 18 Jun 2015 05:29:36 GMT

Hi Prashanth ,

The data is not from Production Environment.

And yes I agree with you that its better to correct the data in source system itself.

But I was suggested to correct the data at DSO level.

Here, m again confused about the settings which I need to follow while loading records into DSO.

I have to create 2 DTPs, Right ?

First DTP to load the correct records. In update tab - what setting should I choose ?

Second DTP i.e ERROR DTP to load the corrected records of wrong date. Here again, in update tab - what setting should I choose ?

Have referred many docs and scn posts.

But m confused.

Could u please guide me the step by step flow.

Thanks & Regards,

Shruti Yendigeri

Re: Code to correct Invalid date to correct date format.

Former Member — Thu, 18 Jun 2015 06:33:46 GMT

Hi Shruti,

You have already created on DTP which is Delta/Full. Just goto alredy created DTP and click on Creating Error DTP and Errors Per Pack. as per requirement like 1000.

Please check below DTP,

It will create automatically error DTP. Then, Set Error Handling based on requirement.

You can set Valid Records Update, Reporting Possible ( Request Green).

Edit the records and error stack and run the Error DTP.

Hope this helps.

Chandresh

Re: Code to correct Invalid date to correct date format.

Former Member — Thu, 18 Jun 2015 18:51:15 GMT

Shruti,

What is the format of date in the source... ? How is it coming across

In your data source - you might find a conversion exit against the field - what is it ...? is there one currently ? ( usually PERIV ... or something of that sort )

Then the best way is to convert it to YYYYMMDD in the routine and it will get updated - but if we are going to guess the date format by looking at what is available in the DSO / Cube - you will have a tough time trying to deduce the format...

My 0.02

Arun