Forum Stats

  • 3,814,410 Users
  • 2,258,870 Discussions
  • 7,892,703 Comments

Discussions

Multiple amount load in data management

Ashima Rajput
Ashima Rajput Member Posts: 69
edited Apr 1, 2019 8:17AM in Financial Data Management

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

Ashima Rajput

Best Answer

  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Mar 27, 2019 7:16AM Answer ✓

    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 RajputAshima Rajput

Answers

  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Mar 26, 2019 8:38AM

    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 RajputAshima Rajput
  • Ashima Rajput
    Ashima Rajput Member Posts: 69
    edited Mar 26, 2019 9:05AM

    Can you please tell me the script example.

    i have added lookup dimension.

    Working on cloud.

    Thanks

    Ashima

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 26, 2019 9:12AM

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

    Ashima RajputAshima Rajput
  • Ashima Rajput
    Ashima Rajput Member Posts: 69
    edited Mar 27, 2019 2:50AM

    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.

    pastedImage_0.png

    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
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 27, 2019 3:58AM

    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"

    Ashima RajputAshima Rajput
  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Mar 27, 2019 7:16AM Answer ✓

    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 RajputAshima Rajput
  • Ashima Rajput
    Ashima Rajput Member Posts: 69
    edited Mar 28, 2019 12:30PM

    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

    pastedImage_0.png

    i have tried AMOUNT,AMOUNTX

    nothing is working.

    can you guys please help on this.

    thanks

    ashima

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Mar 28, 2019 12:37PM

    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
  • Ashima Rajput
    Ashima Rajput Member Posts: 69
    edited Apr 1, 2019 8:15AM

    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
    User_CRIZN Member Posts: 1 Green Ribbon

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