Oracle Database Discussions

DRCRSplit loads either the debit or the credit amount not both.

If this is On Prem: You can either write an import script that accumulates the values so that you have a single import record (put the debit and credit amounts in attribute or lookup columns so you have visibility of the amounts) or import the debit amount and then write a script to create another import record with the credit amount. The first is easier than the second but the second has better visibility.

If this is Cloud: Map the credit amount to an attribute or lookup column then write a SQL map on a dimension that will execute for all source records. In that SQL map set the AMOUNTX column to be Amount plus (or minus) the column that contains the credit amount.

There may be other ways of doing it.

Can you please tell me the script example.

i have added lookup dimension.

Working on cloud.

Thanks

Ashima

There is an example of how to use SQL mapping scripts in the documentation - Creating Mapping Scripts

Thanks for your reply @"JohnGoodwin" and @"user6692921".

it helps me alot.

I have a question again so can we use DRCRsplit with csv if yes then how can we define midpoint. i have tried but not get success.

i have added lookup dimension as credit amount.

but when i'm trying to write an sql script on credit amount which is UD6, amount not getting reflected.

only want to clarify is this the correct way to do this.

so can we write script like this.

Amountx=UD6-AMOUNT

Please help me with this.

by adding any points you guys can save my day.

Thanks in advance

Thanks

Ashima

You should be checking the process log, there might be a SQL error in there

If you are putting the SQL mapping on the lookup dimension you probably want something like, note the UD6 to start with so the SQL validates.

UD6, AMOUNTX = LOGICHERE

You may also need to convert UD6 to be a number if you including it in the above "LOGICHERE"

Hi All,

I'm writing this code in credit amount.

but this code is not running.

SET AMOUNT=

CASE

WHEN UD6 IS NOT NULL THEN CAST(REPLACE(UD6, ',', '') AS INT)-AMOUNT

ELSE AMOUNT

END

but when i'm writing this code running successfully but the issue is it gets storing result into credit amount.

but i need to load amount which is on data column.

CASE

WHEN UD6 IS NOT NULL THEN CAST(REPLACE(UD6, ',', '') AS INT)-AMOUNT

ELSE AMOUNT

END

i have tried AMOUNT,AMOUNTX

nothing is working.

can you guys please help on this.

thanks

ashima

it should be the AMOUNTX column, why don't you just to the to_number() function?

Also when you say it is not working you have to provide more information, if the SQL is valid and there are no SQL errors in the log then the SQL will have run.

You should try with the SQL as simple as possible like assigning AMOUNTX a static value, then when it is working expand the SQL.

For example:

UD6, AMOUNTX = 99.99

Hi All,

This is the final script by using this i was able to load.

UD6,

AMOUNTX=TO_NUMBER(REPLACE(UD6, ',', ''))-AMOUNT

I would like to say special thanks to   and .

Thanks

Ashima

but what do you put in IMPORT FORMAT, in dimension Amount?

Hi Experts, Thanks for the solution. I am implementing similar case .
Debit is mapped to Amount and Credit is mapped to Lookup(UD10), However it is skipping the rows where debit is null(BLANK row skipped error in log). Can you please help suggest on this?
Thanks,

Receiving the same error for the columns with Debit as blank, Could you please suggest the solution that worked?

@ashima-rajput I am also receiving the same error when i tried to implement the same please suggest