Multiple amount load in data management

Ashima Rajput

Hi All,

I need to load data separately for two columns debit and credit through data management into FCCS.

for account 10-000 need to load 121.

for account 100-00 need to load 23423.

How we can load this debit credit load through csv file.

    10-000       Names         Debit       Credit             

    100-00       x                      121         42342                      

    100-01       yz                    234         23423     

    100-02        zzz                 3568       3255

i have gone through this link but it is for fixed format.

https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/erpia/displaying_debit_and_credit_columns…

Thanks

Ashima

user6692921

You don't use DRCRSplit. You set the debit field to be the Amount field and the Credit field to be the lookup (UD6). Then the SQL map does the calculation: AmountX = UD6 - Amount

As John says you might have to convert UD6 to a number and you might need to check for NULL. The syntax for that will vary depending on whether you have SQL Server or Oracle.

user6692921

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.

Ashima Rajput

Can you please tell me the script example.

i have added lookup dimension.

Working on cloud.

Thanks

Ashima

JohnGoodwin

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

Ashima Rajput

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

JohnGoodwin

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"

user6692921

You don't use DRCRSplit. You set the debit field to be the Amount field and the Credit field to be the lookup (UD6). Then the SQL map does the calculation: AmountX = UD6 - Amount

As John says you might have to convert UD6 to a number and you might need to check for NULL. The syntax for that will vary depending on whether you have SQL Server or Oracle.

Ashima Rajput

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

JohnGoodwin

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

Ashima Rajput

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

User_CRIZN

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