11 Replies Latest reply: Nov 19, 2015 8:29 AM by Paolo Ferri RSS

    - 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

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

          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

            • 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
                  Stefan Wühl

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

                    • 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

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

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

                            • 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

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

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

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

                        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;

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

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

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