topic Adding two fields in the script in QlikView

Adding two fields in the script

Tue, 09 Jun 2015 14:09:31 GMT

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;

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 14:12:26 GMT

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

Re: Adding two fields in the script

Tue, 09 Jun 2015 14:21:49 GMT

Hi Sunny, I´m doing a join

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

Re: Adding two fields in the script

Gabriel — Tue, 09 Jun 2015 14:27:51 GMT

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;

Re: Adding two fields in the script

Tue, 09 Jun 2015 14:31:02 GMT

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?

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 14:31:59 GMT

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

Re: Adding two fields in the script

Tue, 09 Jun 2015 14:36:07 GMT

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.

Re: Adding two fields in the script

Gabriel — Tue, 09 Jun 2015 14:38:18 GMT

Hi,

Please post sample file (.qvw file)

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 14:40:10 GMT

What about when you just do

SecondaryCost.ValueInJan as Value

'001' as Month

Does it still work?

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 14:40:32 GMT

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

Re: Adding two fields in the script

Tue, 09 Jun 2015 14:42:01 GMT

It loads but shows a '-' as value in the table viewer.

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 14:46:16 GMT

What about when you just do

SecondaryCost.ValueInJan as Value

'001' as Month

Does it still work?

Re: Adding two fields in the script

Tue, 09 Jun 2015 14:48:54 GMT

Yes, each field individually works!

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 14:52:59 GMT

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,

'001' as Month

Resident PrimaryCost;

See if this helps

Re: Adding two fields in the script

Tue, 09 Jun 2015 14:58:05 GMT

Thanks Sunny. I already did that, the fields are numeric and are treated as such in tables etc.

Re: Adding two fields in the script

sunny_talwar — Tue, 09 Jun 2015 15:00:36 GMT

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.

Re: Adding two fields in the script

Tue, 09 Jun 2015 15:16:58 GMT

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?

Re: Adding two fields in the script

Gabriel — Tue, 09 Jun 2015 15:28:38 GMT

Hi,

What I would recommend doing is to use APPLYMAP.

Re: Adding two fields in the script

Tue, 09 Jun 2015 15:47:12 GMT

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

load
Year

Company

CostCenter

Account

ValueInJan as Value,

'001' as Month