Currency Conversion Issue - Add Rate Where Null and Calculate

[#1]

Hello,

I am trying to convert Unit Price into USD using the RATE_MULT field. My conversion table does not have a value for USD prior to April 2012. Where this value is null in the RATE_MULT column, I have replaced it with Num#(1) using Calculated Dimension. The problem comes about then when I try to calculate an expression (USD Amount) off of the calculated dimension. The calculation does not recognize the new (1), and only a null value is returned in USD Amount.

Perhaps the synthetic key is the issue. I had to join on MonthYear as well as Currency Paid due to the dates not being exact matches.

I have attached the file here. Thanks for your assistance.

[whiteline · Accepted solution]

All that you did in calculated dimension remains there.

In your expression you use field RATE_MULT. You can't refer to dimensions by label.

Use column(N) function or just add if statement in your expression:

=([Unit Price Paid]*if(isnull(RATE_MULT),1,RATE_MULT))*[Quantity, lbs]

Also, consider replace nulls with 1 in script as you don't use them:

LOAD EFFDT,

Month(EFFDT)&'-'&Year(EFFDT) as MonthYear,

     FROM_CUR as [Currency Paid],

     if(isnull(RATE_MULT),1,RATE_MULT) as RATE_MULT

FROM

[Currency Table.xls]

(biff, embedded labels, table is Sheet1$);

[#3]

The expression statement worked, Thank You!  I will also try the load script method, but this is enough to keep me moving forward.