Need to upload for the same POV and account different detailed values ?
Very often we need to upload detailed amount value instead of total value to be able to have the composition of the total amount, here AA Balance and GP Balance instead of TL Balance.
How to do that?
The solution is to load both information in the standard way, ie one line in FDMEE and then duplicate this line while switching values.
Let’s go further.
Step 1 :
In “Target application”, in addition to standard dimensions, we add a dimension field of type “LOOKUP” to get the second value which is associated to UDx data table column, here UD6:
Step 2 :
According to the following file :
Entity;BU;Acc;Sub;Acc Desc;AA_Balance;GP_Balance;TL_Balance 00765;765000002000;1020;1;First Curr Balance;50000.00;1000.00;51000.00 00730;730000002000;1020;11;Second Curr Balance;30000.00;5000.00;35000.00 00765;765000002000;1020;11;First DKK Balance;75600.00;789.00;76389.00
In “Import format” in addition to “standard” amount, here AA_Balance, add a new line to retrieve GP_Balance:
Step 3 :
To prevent the lines being summed, we have to specify a custom field (C4 for exemple).
In the first part of the query, we specify the fields. Then in the select we will get in place of AMOUNT, AMOUNTX, the UDx we choose above, here UD6. Here as we want to identify the lines we change the value of C4 (UD4) with “C4GP”. And finally, UD6 and UD6X, no longer needed, are set to empty value.
This works fine with SQL Server. With Oracle Database, DATAKEY is mandatory so the beginning of the select query looks like:
|query = query + ” select DATAKEY + (select count(DATAKEY) from FDM.TDATASEG_T where LOADID= ? ), LOADID …|
Let’s see the result
Note: be careful if you are using “100,00” number formatting instead of “100.00”. For amount you can use Fill=EuroToUs directive in import format to convert numbers. UDx dimensions are nvchar values so they accept “100,00” string. But you will get an error when converting to decimal. To avoid that either you put a script in import format for the second value to convert it to numbers:
def GP(strField, strRecord): if strField == "": return strField else: return strField.replace(",",".")
or modify the query by replacing:
case when UD6 = '' THEN 0 else cast(UD6 as decimal(29,12)) end
case when UD6 = '' THEN 0 else cast(REPLACE(UD6, ',' , '.') as decimal(29,12)) end