One of the most recurrent problem when loading data is to map accounts depending on the sign of the amount.
There’s two simple ways to deal with that: Jython scripting or SQL scripting.
In cloud version of FDMEE (Data Management), Jython scripting is not allowed so SQL scripting is mandatory.
In a “in”, “between” or “like” mapping, use the #SCRIPT statement.
The first line retrieve AMOUNT
montant = float(fdmRow.getString(“AMOUNT”)), don’t forget to cast in float otherwise there’s no error but a test on a string (as fdmRow.getString gets a string) gives unpredictable results !
Then a test on the sign:
if montant > 0: if negative the amount sign is change
fdmResultMap[“AMOUNTX”] = montant * -1
In the same way, in a “in”, “between” or “like” mapping, use the #SQL statement.
Here the line is directly converted during query in database.
There’s 2 statements, one for the mapping itself : CASE WHEN AMOUNT > 0 then ‘A4600’ ELSE ‘L40100’ END
and a second to change sign: ,AMOUNTX=CASE WHEN AMOUNT <0 THEN AMOUNT*-1 ELSE AMOUNT END.