3 Replies Latest reply: Jan 22, 2016 2:12 PM by Stefan Wühl RSS

    Using TOTAL Function in Script

    Zach Paz

      Hi All,

       

      I need to sum up a field by two other fields. See example below.

       

      SUM(TOTAL <cip,geoid> [2014 Employment]) as CIPbyGeoidWage

       

      The issue is that the script does not like the second field in the TOTAL function. <cip,geoid>

       

      Is there a way to sum based on aggregating by two fields in the load script? From what i can tell, the formula works if I only have one field like, <cip>, but i need the data aggregated by both (<cip, geoid>).

       

      Thanks in advance!

       

      Zach

        • Re: Using TOTAL Function in Script
          Oleg Troyansky

          Hi Zach,

           

          I'm surprised that only the second attribute is being rejected. In my mind, TOTAL should not work at all in the script, because it's a Chart feature.

           

          In the script, we use GROUP BY load for aggregating data:

           

          LOAD

               cip,

               geoid,

               SUM( [2014 Employment]) as CIPbyGeoidWage

          RESIDENT

               MyTable

          GROUP BY

               cip,

               geoid

          ;


          Cheers,

          Oleg Troyansky

          Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

          • Re: Using TOTAL Function in Script
            Sunny Talwar

            I was not aware that TOTAL keyword is allowed in script at all.

             

            I would usually do this like this:

             

            Left Join (Table)

            LOAD cip,

                      geoid

                      Sum([2014 Employment]) as CIPbyGeoidWage

            Resident Table

            Group By cip, geoid;

            • Re: Using TOTAL Function in Script
              Stefan Wühl

               

              Is there a way to sum based on aggregating by two fields in the load script? From what i can tell, the formula works if I only have one field like, <cip>, but i need the data aggregated by both (<cip, geoid>).

               

              Zach,

               

              could you post a sample QVW or script that demonstrates a Sum(TOTAL FIELD) or Sum(TOTAL<cip> FIELD)?

              As already pointed out by others I don't think TOTAL qualifier is allowed in the script (don't get confused by the syntax checker without running the script).

              If you made it work, I would be interested to see your sample.

               

              Stefan