If the Transactions table and [Conversion Rate] table are linked by the currency code field, you can calculate the Base Amount in the charts, using the expression given by you.
If you want to do the calculation in the load script, you may have to bring the ConversionRate to the Transactions table using a left join or mapping load of ConversionRate and then calculate the Base Amount in the script.
Hope this helps.
I could get to create a calculated field when creating charts using expression (ActualAmount * ConversionRate), but wanted to see if there is a way i can include this calculation in the Load Script so User no need to compute this logic each time and have him/her pull from Available Fields.
As per your suggestion, I could bring the ConversionRate table to Transactions Tab and script as join and then calculate the BaseAmoun. Can you please explain on second option you mentioned- "mapping load of ConversionRate" . I wanted to try this option also and see which would be best for our needs.
Please see if the attached example helps.
The code used in the example for mapping load is:
Mapping LOAD * Inline [
ApplyMap('ConversionRateMapping',Cur,1) as ConvRate,
ApplyMap('ConversionRateMapping',Cur,1)*Amt as BaseAmount;
LOAD * Inline [
ID, Cur, Amt
You have two options for Mapping.
Imagine you have only numbers for your items and you also want to see the item-name in the table2
The first option:
MAP name USING table;
=> so you have the item-names as value not the numbers.
APPLYMAP('table', item) AS name
=> this option is very useful, if you haven't got the field 'name' in your table.
Important notice: Mapping tables have only two fields.