Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
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