Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good afternoon,
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,
'001' as Month
resident PrimaryCost;
I don't see the script for where you are joining SecondaryCost into PrimaryCost? Are you doing a join or a concatenate? Can you post your script for the portion of the script?
Best,
Sunny
Hi Sunny, I´m doing a join
join (PrimaryCost)
load *
resident SecondaryCost;
drop tables SecondaryCost;
Hi,
In your script, your join is OUTER JOIN. Is this what you want. This could be the reason why you have - as in NULL value.
join (PrimaryCost)
load *
resident SecondaryCost;
drop tables SecondaryCost;
Hi Gabriel,
yes, an outer join is what I´d like to have from a logical point of view. How can I make the sum work nevertheless?
PrimaryCost.ValueInJan + SecondaryCost.ValueInJan as Value,
Are you using the table names because you have used QUALIFY statement in your join? or is it just to specify which table you are pulling it from???
I think this is what you probably need
QUALIFY ValueInJan, ValueInFeb.....;
PrimaryCost:
LOAD Year
Company
CostCenter
Account
ValueInJan
ValueInFeb
....
ValueInDec
FROM ...
SecondaryCost:
NoConcatenate
LOAD Year
Company
CostCenter
Account
ValueInJan
ValueInFeb
....
ValueInDec
FROM ...
UNQUALIFY ValueInJan, ValueInFeb.....;
Join (PrimaryCost)
LOAD *
Resident SecondaryCost;
drop tables SecondaryCost;
FinalTable:
LOAD *,
PrimaryCost.ValueInJan + SecondaryCost.ValueInJan as Value,
'001' as Month
Resident PrimaryCost;
I hope this will help.
Best,
Sunny
Thanks Sunny. I´m already using all of that; it is the + that doesn´t work. If I don´t use the + i.e. if I just write
PrimaryCost.ValueInJan as Value
'001' as Month
then everything works nicely.
Hi,
Please post sample file (.qvw file)
What about when you just do
SecondaryCost.ValueInJan as Value
'001' as Month
Does it still work?
and in-addition, is it giving you an error or is it reloading but not adding up???