Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

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

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

Not applicable

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

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..

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

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]')

MVP
MVP

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

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]"

Not applicable

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

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);

2015-11-18_1750.png

MVP
MVP

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

it seems to work, see attachment

1.png

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;

MVP
MVP

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

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);

Not applicable

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

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

Not applicable

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

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...

Community Browser