4 Replies Latest reply: Nov 28, 2016 2:06 PM by Todd Johnston RSS

    Aggregating in Load Script

    Todd Johnston

      Hello All,

       

      I have reviewed several other discussions regarding this and haven't come across a solution.  I am trying to create a field that will identify how many Employees a Client has in their entire Company and label it as [CompanyEmployees].  As a rule of thumb, I can have several Clients within a single Company. My data source is structured as such that we only report on the number of employees at a Client level, not at the Company level.  Is it possible to accomplish this within the load script?  I would then like to categorize each Client based on the [CompanyEmployees] number.  eg 1-10,11-24, etc. 

       

          

      Client Company# of Employees (Client Level)

      # of Employees (Company Level)

      TRYING TO CALC

      111020
      211020
      32525
      42525
      52525
      62525
      72525

       

      Hopefully this makes sense.

       

      As usual, thanks for the help.

       

      TJ

        • Re: Aggregating in Load Script
          Nicole Smith

          Load Script like this will give you what you have detailed in your original post:

           

          Data:
          LOAD * INLINE [
              Client, Company, # of Employees (Client Level)
              1, 1, 10
              2, 1, 10
              3, 2, 5
              4, 2, 5
              5, 2, 5
              6, 2, 5
              7, 2, 5
          ];
          
          LEFT JOIN (Data)
          LOAD Company,
            sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]
          RESIDENT Data
          GROUP BY Company;
          
          
          

           

          However, I would suggest keeping the Company Level total in a separate table so that your calculations work appropriately:

           

          Data:
          LOAD * INLINE [
              Client, Company, # of Employees (Client Level)
              1, 1, 10
              2, 1, 10
              3, 2, 5
              4, 2, 5
              5, 2, 5
              6, 2, 5
              7, 2, 5
          ];
          
          Company_Totals:
          LOAD Company,
            sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]
          RESIDENT Data
          GROUP BY Company;
          
          
          

           

          Doing it like the latter will make sure that if you use any sums in your calculations, you're not summing the 20 twice and the 25 five times.

           

          Then to classify your numbers into groups, you can use the class function if you want even spaced groups:

          class ‒ QlikView

           

          If you want groups that are unevenly spaced, you will need to write if statements.

          • Re: Aggregating in Load Script
            Todd Johnston

            Thanks Nicole.  I have thousands of clients and companies to calculate this for.  I am pulling the data directly from a qvd.  Is there a way to adjust your statement to accommodate that rather than the INLINE portion?