Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the Straight table as shown below;
I am using Cost Code, Cost Area as Dimensions and Value, Value (USD) as expressions.
Expressions:
Value = Value
Value (USD) = Value_USD (Value/Currency_Rate as Value_USD in my script)
Why I am not able to see any data for Value (USD), Can anyone tell me what am I doing wrong here?
thanks
Bhavesh
Check that Project_Cost_Currency_Key is unique in the Currency_Rate table. And/or check the NoOfRow('Project_Cost') value with trace statements before and after the join. I am pretty sure that the join is doubling the rows.
Can you do like below and if possible can you share a sample to look into?
= Sum(Value)
= Sum(Value_USD)
Else, Use this feature as this enable for Straight table
If you are doing this calculation in script, I suggest that you upload the relevent script portion.. Otherwise its just guesswork.
I did change both the expressions to Sum(Value), Sum(Value_USD) but my values are doubled when I do that
As shown in the image below, my values are doubled when I do Sum of rows feature.
Jonathan, I am creating my table with Cost Code, Cost Area as dimensions and Value, Value_USD as expressions. This is the Calculation I am doing in Script;
Project_Cost:
Load Project_Number &'-'& Region &'-'& if (match(Country_Name,'US','UK','Canada')>0, Country_Name, Capitalize(Country_Name)) &'-'& Snapshot_Date as Project_Cost_Key,
if (match(Capitalize(Country_Name),'Germany','Belgium','France','Italy','Spain','Netherland')>0,'Europe',if (match(Country_Name,'US','UK','Canada')>0, Country_Name, Capitalize(Country_Name))) &'-'& Year(Snapshot_Date) as Project_Cost_Currency_Key,
Cost_Code,
Cost_Area,
Value,
Updated_OEC_Budget
FROM
(qvd);
left join (Project_Cost)
LOAD
if (match(Capitalize(Country_Name),'Germany','Belgium','France','Italy','Spain','Netherland')>0,'Europe',if (match(Country_Name,'US','UK','Canada')>0, Country_Name, Capitalize(Country_Name))) &'-'& year(Snapshot_date) as Project_Cost_Currency_Key,
Currency_Code as Project_Cost_Currency_Code,
Currency_Rate as Project_Cost_Currency_Rate
FROM
(qvd)
where Month(Snapshot_date)='01';
Project_Cost1:
NoConcatenate Load
*,
Value/Project_Cost_Currency_Rate as Value_USD,
Updated_OEC_Budget/Project_Cost_Currency_Rate as Updated_OEC_Budget_USD,
Resident Project_Cost;
drop table Project_Cost;
Check that Project_Cost_Currency_Key is unique in the Currency_Rate table. And/or check the NoOfRow('Project_Cost') value with trace statements before and after the join. I am pretty sure that the join is doubling the rows.
Jonathan, you are right it is not a one-to-one relationship. I have missed another condition in the joining.
where Month(Snapshot_date)='01'
and Currency_Ref = 'USD';
Thank you for your explanation