I´ve got two tables with the same structure, one containing "primary" and the other containing "secondary" costs. The structure is like
PrimaryCost:
Year
Company
CostCenter
Account
ValueInJan
ValueInFeb
....
ValueInDec
I´d like to split this by month adding the values from both tables. I load with a qualify on the values, then I join SecondaryCost into PrimaryCost for easier handling. So far, so good.
Then I set up a final table cost as follows, repeating per month. However, the addition doesn´t work and my field Value is initial - why?
Cost:
load Year
Company
CostCenter
Account
PrimaryCost.ValueInJan + SecondaryCost.ValueInJan as Value,
May be the values are text strings and not numeric values. Can you try changing them to Numeric value using Num#() function. I am using '#', but if the value has commas and two decimals then '#,###.00'.
FinalTable:
LOAD *,
Num#(PrimaryCost.ValueInJan, '#') + Num#(SecondaryCost.ValueInJan, '#') as Value,
I guess Gabsus04 is right then. We would need to see a sample to actually see what is going wrong, because I don't think there is anything wrong with the syntax.
OK, you guys are right. Due to the join, I see with the table viewer on table PrimaryCost records with say value 123 for PrimaryCost.ValueInJan and - for SecondaryCost.ValueInJan. How can I nevertheless add the two? Would you recommend other joins or no joins and if yes, how do you achieve the aforementioned goal?
OK, thanks. The solution was even simpler: I dropped the qualify story, just loaded the primary and the secondary costs directly into one table (that keeps the individual records from the two sources I guess), and then did a load resident like this
