Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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$);
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$);
The expression statement worked, Thank You! I will also try the load script method, but this is enough to keep me moving forward.