Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Multiple amount load in 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.
Thanks
Ashima
Best 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.
Answers
-
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"
-
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.
-
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
-
but what do you put in IMPORT FORMAT, in dimension Amount?