Adding two fields in the script

[#1]

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;

[sunny_talwar]

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

[#3]

Hi Sunny, I´m doing a join

join (PrimaryCost)
load *
resident SecondaryCost;
drop tables SecondaryCost;

[Gabriel]

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;

[#5]

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?

[sunny_talwar]

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

[#7]

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.

[Gabriel]

Hi,

Please post sample file (.qvw file)

[sunny_talwar]

What about when you just do

SecondaryCost.ValueInJan as Value

'001' as Month

Does it still work?

[sunny_talwar]

and in-addition, is it giving you an error or is it reloading but not adding up???