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

[#1]

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

[swuehl]

Try using double quotes around the field names, and also rangesum():

Rangesum("Purchase (Facebook Pixel) [28 Days After Clicking]","Checkout Clicking Auxiliary")

                      as "Checkouts (Conversion Pixel) [28 Days After Clicking]"

Edit: See also

QlikView Quoteology

[#3]

Hi Swehl

Thanks for the reply
Unfortunately it doesn t work

I get an error

Field not found - <Checkout Clicking Auxiliary>

while it was working before...at least, uploading with no errors..

[settu_periasamy]

Hi,

if you want create a variable in Script Try this..

NoConcatenate

Variable_Set:

Load

     Sum([Checkout Clicking Auxiliary])+SUM('Purchase (Facebook Pixel) [28 Days After Clicking]' ) as

    "Checkouts (Conversion Pixel) [28 Days After Clicking]"

Resident YourTable;

LET vCheckout=Peek('Checkouts (Conversion Pixel) [28 Days After Clicking]',0,'Variable_Set');

Drop Table Variable_Set;

Expression : Sum('Checkouts (Conversion Pixel) [28 Days After Clicking]')

[swuehl]

Could you post your excel file or your current script code that loads in the two existing columns?

This line

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

has taken 'Purchase (Facebook Pixel) [28 Days After Clicking]' and 'Checkout Clicking Auxiliary' just as literal string values, not as fields. The sum of two strings is NULL (but not a syntax error).

This should return the concatenation of both literals:

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

[#6]

Hi,
Thanks for your help.
Here is my code and the output on the pivot table

The first 2 columns figures are correct
The third one, previously showing zero, shows now random value.
My goal is to put in the 3rd column the first+the second

LOAD

Date("Reporting Starts") as ddate,

    "Reporting Ends" as "End Date",

    Impressions,

    "Unique Clicks (All)",

    "Amount Spent (GBP)",

    "Advert Name" as ADID,

    "Checkouts (Conversion Pixel) [28 Days After Clicking]" as "Checkout Clicking Auxiliary", //I am changing name to this column because then I want to recode within Purchase + Checkouts

"Registrations (Conversion Pixel) [28 Days After Clicking]",

     "Checkouts (Conversion Pixel) [28 Days After Viewing]",

    "Registrations (Conversion Pixel) [28 Days After Viewing]",

    "Purchase (Facebook Pixel) [28 Days After Clicking]",

    "Purchase (Facebook Pixel) [28 Days After Viewing]",

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

FROM [lib://Documents//daily/cdaily.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[maxgro]

it seems to work, see attachment

test data

X:

  load

  date(makedate(2015) + recno()) as date,

  *,

  "Purchase (Facebook Pixel) [28 Days After Clicking]"

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

load

  floor(rand()*100) as [Checkout Clicking Auxiliary],

  floor(rand()*10) as  "Purchase (Facebook Pixel) [28 Days After Clicking]"

autogenerate 100;

[swuehl]

And this gives you an error, field not found?

(You can't reference the aliased field in the same LOAD statement, only in a preceding load).

LOAD

Date("Reporting Starts") as ddate,

    "Reporting Ends" as "End Date",

    Impressions,

    "Unique Clicks (All)",

    "Amount Spent (GBP)",

    "Advert Name" as ADID,

    "Checkouts (Conversion Pixel) [28 Days After Clicking]" as "Checkout Clicking Auxiliary", //I am changing name to this column because then I want to recode within Purchase + Checkouts

"Registrations (Conversion Pixel) [28 Days After Clicking]",

     "Checkouts (Conversion Pixel) [28 Days After Viewing]",

    "Registrations (Conversion Pixel) [28 Days After Viewing]",

    "Purchase (Facebook Pixel) [28 Days After Clicking]",

    "Purchase (Facebook Pixel) [28 Days After Viewing]",

Rangesum("Purchase (Facebook Pixel) [28 Days After Clicking]"," "Checkouts (Conversion Pixel) [28 Days After Clicking]")  as "Checkouts (Conversion Pixel) [28 Days After Clicking]"

FROM [lib://Documents//daily/cdaily.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

edit:

With precding load you can reference the new field name:

LOAD *,

     Rangesum("Purchase (Facebook Pixel) [28 Days After Clicking]"," "Checkout Clicking Auxiliary")  as "Checkouts (Conversion Pixel) [28 Days After Clicking]";

LOAD

Date("Reporting Starts") as ddate,

    "Reporting Ends" as "End Date",

    Impressions,

    "Unique Clicks (All)",

    "Amount Spent (GBP)",

    "Advert Name" as ADID,

    "Checkouts (Conversion Pixel) [28 Days After Clicking]" as "Checkout Clicking Auxiliary", //I am changing name to this column because then I want to recode within Purchase + Checkouts

"Registrations (Conversion Pixel) [28 Days After Clicking]",

     "Checkouts (Conversion Pixel) [28 Days After Viewing]",

    "Registrations (Conversion Pixel) [28 Days After Viewing]",

    "Purchase (Facebook Pixel) [28 Days After Clicking]",

    "Purchase (Facebook Pixel) [28 Days After Viewing]"

FROM [lib://Documents//daily/cdaily.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[#9]

Hi,

I tried to do it, meaning that I had written the whole code that you wrote
the first time without any sum, the second time adding

LOAD *,

     Rangesum("Purchase (Facebook Pixel) [28 Days After Clicking]"," "Checkout Clicking Auxiliary")  as "Checkouts (Conversion Pixel) [28 Days After Clicking]";

and then again the code.

I just get the same random results as before.

Question: why do you say that I cannot compute the sum the first time? Isn't it possible at all with 1 or 2 lines of codes as most of the other programming language? I basically use Qlick to upload files (preformatted in excel, therefore I am not very good at coding in Qlik, but I have programmed in several languages)

Thank you for your help

[#10]

Hi Maxgro,

I cannot touch the date since everything is already aggregated by date.
Let me explain: this app is already live and has 10 Facebook accounts on it
everyone has the same script structure and they are concatenated by a specific common parameter

Now we added one column to the reports that we export everyday, which is purchase

so everything that I need to do is to add to this specific account a new column

Like, if I did it in excel and I had column A and column B, I would create manually column C which is the sum of the previous 2

This is what I want to do, so I was wondering if it was possible to do it in the script just saying something like

COLUMN A + COLUMN B = COLUMN C, in order to have a SUM column

I don't need to aggregate data since we have already created dimensions, measures, master items etc..

Ironically, I have done it with the subtraction and it worked without any issue. Today I am on this problem since 2 (and here in UK is now 7).

Looks very simple, but I can't figure it out...

Let me know if you have any solution...