Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

- ISSUE: Sum 2 columns of the same table in the data load editor and code the results in a new column

Hi Guys,

Simple questions I don't manage to solve:

I am working on an excel file exported from Facebook.
I have 2 columns in a table

One is called  Checkout Clicking Auxiliary

the other is called Purchase (Facebook Pixel) [28 Days After Clicking]

what I want to do is to create a new variable which is the sum of the two, called Checkouts (Conversion Pixel) [28 Days After Clicking]

I tried to do in the data load editor

('Purchase (Facebook Pixel) [28 Days After Clicking]' + 'Checkout Clicking Auxiliary')  as "Checkouts (Conversion Pixel) [28 Days After Clicking]"

But it doesn't work. The scrip is uploaded correctly, but if I try to plot a pivot table

with these 3 columns and look at the day by day results

SUM("Checkout Clicking Auxiliary") ---->this works and shows results in line with Facebook

SUM("Purchase (Facebook Pixel) [28 Days After Clicking]") ---->this works and shows results in line with Facebook

SUM("Checkouts (Conversion Pixel) [28 Days After Clicking]") -----> this shows all 0

Any idea about this? I believe that the issue is related to the syntax.
Believe it or not, I have been searching about the answer on line for 1 hour and I didn't find it.

Any help would be much appreciated,

Best,

Paolo

11 Replies
swuehl
MVP
MVP

I think there are three things to consider here:

1) quoting of field names

See the above referenced blog post by HIC. If you want to reference a field that contains some special characters in its name like spaces, use double quotes around the field name. Single quotes are used for literals in QV.

2) If you rename fields or create calculated fields using AS, you can't reference this new field name in the same LOAD. Use e.g. a preceding LOAD, where you can start using the new field.

3) If you add two field, and one of the fields could be NULL, the addition operator will return NULL in these cases. If you want to return at least the other operand, use Rangesum

This might not explain why you see 'random values' returned from the code I posted. Could you upload your app or the excel file you are loading (could contain some mock up data, if that's required).

Not applicable
Author

Hi,

Thanks a lot for the explanations, it was very helpful to understand something more
I have just started using Qlik and even if I have also used several programming languages I find it very different and difficult, even when it comes to simple stuff.

I think I am going to work around by modifying the master items, it looks more simple..

Best Regards,

Paolo