Importing data of format dd/mm/yyyy into BPC

[former_member570917]

Hi all,

We have a load file supplied to us from a separate HR system that supplies data in the format "dd/mm/yyyy". We need to load this into the system as yyyy.dd so that BPC can store it as a numerical value.

I am comfortable with how I would convert dd/mm/yyyy to yyyy/mm through javascript in the conversion file (see example at https://jsfiddle.net/31c7ywdp/). However, whenever I run or validate this against the data file, it just always throws an error saying Line5 :Dimension:SIGNEDDATA member:06/05/2019 is non-numerical in line 5; rejected.

Now, I'm worried that the transformation/conversion process perhaps does an initial check that any "AMOUNT" column is already in numerical format - but am hoping that someone out there can suggest a way of getting around this? It makes sense to me that you could receive something not necessarily in a numerical format and convert it to a numerical format. Anyone done this in BPC before?

I know that I could either go back to the team running the HR system and ask if they can provide it in a different format (not convinced they can) or instead just either manually manipulate the file in Excel before upload or even write a bit of VBA that will do this for the end users, I'd just prefer it if we didn't have these steps.

I've uploaded a screenshot of both the transformation file I'm trying to use and the javascript in the conversion file.

Thanks

Joe

[ib_m · Accepted solution]

Hi Joe,

I had a similar requirement a while ago and noticed that if you use SIGNEDDATA the numeric data type validation doesn't occur so that, following Vadim's example:

AMOUNT=*COL(10,2:2) doesn't work

but

SIGNEDDATA=*COL(10,2:2) does (tested on BPC 10.1 NW, BW 7.5 SP7)

You can't use javascript conversion but can potentially write the same in the mapping section:

SIGNEDDATA=*IF(ACCOUNT=*STR(HRDB2083) THEN DATE(7:10) + *str(.) + DATE(4:5);DATE)

[former_member186338 · Accepted solution]

Perfect finding!

No idea why it's working with SIGNEDDATA but:

COL(10)=06/05/2019

MAPPING

SIGNEDDATA=*COL(10,2:2)

Saved "6" without issues!

In this particular case (import without header) the formula will be:

SIGNEDDATA=*COL(7,7:10)+*STR(.)+*COL(7,4:5)

Result:

for text file in my prev answer.

[former_member570917 · Accepted solution]

Hi Ibai

Nice to hear from you! 🙂

That's great - nice workaround, will give it a go.

Thanks

Joe

[former_member186338]

By the way, when I want to store date as signeddata in BPC, I prefer to store it as integer calculated based on Excel rules - number of days since 01.01.1900. In this case in the report the column can be formatted as date.

Sample JavaScript calculation is:

var Value = "06/05/2019";
alert( Date.parse(Value.substr(6,4)+"-"+Value.substr(3,2)+"-"+Value.substr(0,2))/86400000 + 25569);

same calculation can be done in ABAP.

[former_member570917]

Agreed Vadim - we actually have both. But the data in the HR system won't work with Excel values and they can't output them - so another thing I am trying to do is convert from dd/mm/yyyy to the Excel number. Funnily enough I had already been experimenting with js and we came up with something very similar to yours (but explicitly showed the js Date function of 01/01/1900 to try to make it that little bit clearer).

We have both types (excel value and YYYY.MM) as we found it easier to use the former in Excel input forms/schedules (obviously) and the latter in script logic (we have phasing that tries to check if the BPC month is less than or equal to start date etc - it just seemed easier to store YYYY.MM and compare it against the time member ID than messing around with Excel etc).

So - got to join all these pieces together now but seems like you and Ibai have pretty much got me there 🙂

[former_member186338]

joe.mitchell

Script logic like:

*REC(EXPRESSION=(%VALUE%>%PER%) ? ...

where %PER% is like 2019.05 ...

resulting in FOR/NEXT loop if multiple periods... can be slow!

I recommend using Custom logic badi in this case instead of script logic. In custom logic badi it's easy to compare Excel integer date with time member.

[former_member570917]

Thanks Vadim.

Out of interest have you got your Javascript to convert dates to excel to work in practice? For me, if I use Date.parse(...) it returns NAN, if I just use Date(...) it returns null and if I use new Date(...) it throws an evaluation error.

Finding it strange because I can get these working on javascript testers, but wondering if BPC maybe uses some type of js that doesn't accept the Date function.

Thanks

Joe

[former_member186338]

joe.mitchell

My JavaScript is working fine in online testers. But I didn't tested it in conversion file - I assume that conversion will not work for not numerical values...

[former_member186338]

I can confirm that there is no way except START ROUTINE badi to perform conversion of some string to numeric value.

[former_member186338]

P.S. The check for numeric data for AMOUNT is performed after START ROUTINE but before conversion.

Even in mapping the following will not work:

COL(10)=06/05/2019

MAPPING

AMOUNT=*COL(10,2:2)

Theoretically has to work with result: 6 (second character in column 10), but not working!

[former_member186338]

Use this badi to debug:

  method IF_UJD_ROUTINE~RUNROUTINE.
* DEBUG =====================================================================
  DATA i_d TYPE C. " for bg debug.
  CLEAR i_d.
  WHILE i_d IS INITIAL.
  ENDWHILE.
* DEBUG =====================================================================
    er_data = ir_data.
  endmethod.

Then in SM50 find the endless loop and start debugging.

[former_member186338]

2 joe.mitchell

Sample debug results - contents of IR_DATA loaded from text file:

Split line to columns and convert last column to required number... 10 minutes job!

[former_member570917]

Haha nice work Vadim. May be easier for you than me with zero BADI knowledge.

Will take a read of the doc and have a stab myself - thanks as ever.

Just for clarity, the BADI gets the text file into the above format (with "06/05/2019") and I would continue to use javascript in the conversion file as I already am? Or are you also suggesting using the BADI to then get "06/05/2019" into 2019.05?

Thanks for the hints

Joe

[former_member186338]

joe.mitchell

START ROUTINE badi will simply load text file in the single column table without any conversion!

LINE field will contain full line.

You have to perform conversion in ABAP, not in JavaScript! But it's also easy!

[former_member570917]

Makes sense. Thanks.

[former_member186338]

I suspect that transformation routine perform check for numeric data type before using conversion!

You can use ROUTINE badi to perform conversion in abap.

[former_member570917]

Thanks Vadim. May leave the ticket unanswered for a little while longer in case anyone comes and says they know how to override this (1 in a million chance, I know). But will bear that in mind - we have some people with BADI capabilities so can always go down that path.

Thanks

Joe

[former_member186338]

joe.mitchell

It's very simple!

You have to use:

STARTROUTINE = BADIFILTERNAME

in transformation Options

Read:

https://www.sap.com/documents/2015/08/20211f55-557c-0010-82c7-eda71af511fa.html

Also, using this badi you can debug the process...