9 Replies Latest reply: Oct 5, 2011 3:18 PM by bryant88 RSS

    Preceding Load and Invalid Expression Error

      I have the following load script:

      LOAD Period,
          
      [Project number],
          
      Description,
          
      Prof.Ctr. as [Profit Centre],
          
      Typ as Type,
          
      Projdef.,
          
      [Ass. emp.],
          
      Pers.No. as [Employee Number],
          
      AutoNumber(Pers.No.) as EmployeeKey,
          
      TRIM([Name of employee or applicant]) as Employee,
          
      Prctr. as [Project Centre],
          
      [Cost Ctr] as [Cost Centre],
          
      PK,
          
      BK,
          
      Task,
          
      Remark,
          
      Status,
          
      Link,
          
      Crcy as Currency,
          
      [Wage Type Long Text],
          
      [Start pro.] as [Project start date],
          
      [End date] as [Project end date],
          
      Date,
          
      [Created on],
          
      [Changed on],
          
      [Project time],
          
      [T&D trv. time],
          
      [Project time1],
          
      [Planned hours],
          
      FTE,
          
      WTE,
          
      Resource,
          
      Cost,
          
      Amount,
          
      Area,
          
      [Time Category],
          
      [Time category group],
          
      FYPeriod,
          
      If([Time category group]= 'Billable', [Project time],
             
      If([Time category group]= 'Productive', [Project time])) as BillProd
                           FROM
      [C:\Documents and Settings\My Documents\Qlikview\Consolidated Data.xlsm]
      (
      ooxml, embedded labels, table is [Utilisation]);

       

       

      I want to obtain the sum of BillProd , divide it by the sum of [Project time] for each employee.

      As a first step I tried:

      LOAD *,
          
      sum(BillProd) AS TotalBP
           
      GROUP BY EmployeeKey;LOAD Period,

      Etc..

      This gives me an Invalid expression error. I imagine I need to do something with the Group By statement, but I am not sure what the determinates of Group By are.

      Bryant

        • Preceding Load and Invalid Expression Error
          Stefan Wühl

          Hi,

           

          you can't use a

           

          LOAD *,

           

          with a group by EmployeeKey clause.

          You need to use an aggregation function for each field not listed in the group by.

           

          Try removing the *, using maybe only EmployeeKey and your sum(BillProd) and then joing the resulting table back to your original table.

           

          Hope this helps,

          Stefan

          • Preceding Load and Invalid Expression Error
            Stefan Wühl

            Hi,

             

            to be more clear,

             

            I would first do a load of your original data, like

             

            [Fact]:

            LOAD

                 Period,

            ...

            From ...;

             

            Then I would try to add in the script a

             

            Left Join (Fact) LOAD

            EmployeeKey,

            sum(BillProd) as TotalBP,

            sum([Project Time]) as TotalPT,

            sum(BillProd) / sum([Project Time]) as Avg

            resident Fact group by EmployeeKey;

             

            Regards,

            Stefan

              • Preceding Load and Invalid Expression Error

                Stefan,

                Thanks for your response. I subsequently found that you cannot do a Group By on a preceding load.
                I have tried using a Join to create what I want, but I am still having trouble with my Group By’s
                I have added the Following:


                Percentage:
                Join (Projects)
                LOAD DISTINCT
                EmployeeKey,
                Month,
                sum(BillProd)/sum([Project time]) AS PercentageBP
                Resident Projects
                GROUP BY EmployeeKey, Month;

                RAG:
                LOAD DISTINCT
                EmployeeKey,
                If(PercentageBP < 0.49999,1,0) as Red
                Resident Projects;

                If I Group by EmployeeKey only I cannot divide my results by the Month. If I add Month to the group by I get multiple answers for every Employee and the subsequent RAG calculation results in :
                -all employees that only months with one will have one Red entry per month
                -All employees with some month 1 and others 0 will have a 1 and a 0 Red entry for every month
                -all employees that only months with zero will have one Red entry per month
                I would like to do sum(Red) per month but I can’t!!
                Am I missing something obvious?
                Bryant

                  • Preceding Load and Invalid Expression Error
                    Sunil Chauhan

                    invalid expression error always caused by aggregrate funtion and group by

                     

                     

                    you need to include all field except used in sum function  in Group by

                     

                     

                    ex:

                     

                     

                    Load

                    a

                    b

                    c,

                    sum(d) as d

                    resident path

                    gropu by a,b,c;

                     

                     

                    if u miss any of three (a,b,c)then it will cause invaalid expression

                    • Re: Preceding Load and Invalid Expression Error
                      Stefan Wühl

                      Bryant,

                       

                      sorry, I am a bit slow today...

                       

                      You want to sum(Red) per Month across Employees? Then maybe use another

                       

                      RAG:

                      LOAD

                      Month,

                      sum(If(PercentageBP < 0.49999,1,0)) as SumRedMonth

                      Resident Projects group by Month;

                       

                      If not, could you give a small example (as table here in the post or attached sample file)?

                        • Re: Preceding Load and Invalid Expression Error

                          Hi Stefan,


                          I think I have confused the issue.
                          A simplified version of my input table
                          Month, Employee, Time, Category Group, Project Time
                          1 Smith Billable 4
                          1 Smith Other 10
                          2 Smith Billable 7
                          1 Jones Billable 9
                          1 Jones Other 2
                          2 Jones Billable 1
                          2 Jones Other 3
                          3 Jones Billable 12
                          3 Smith Billable 11

                          I want to calculate the count of employees in ‘Red’ for each month
                          Month 1 – 1 in Red (Smith 0.4)
                          Month 2 – 1 in Red (Jones 0.25)
                          Month 3 – 0 in Red
                          (My current script would give me a 0 and 1 Red flag for both Smith and Jones for each month which is obviously wrong)

                          My script is as follows:

                          Projects:
                          LOAD Period,
                          [Project number],
                          Description,
                          Prof.Ctr. as [Profit Centre],
                          Typ as Type,
                          Projdef.,
                          [Ass. emp.],
                          Pers.No. as [Employee Number],
                          AutoNumber(Pers.No.) as EmployeeKey,
                          TRIM([Name of employee or applicant]) as Employee,
                          Prctr. as [Project Centre],
                          [Cost Ctr] as [Cost Centre],
                          PK,
                          BK,
                          Task,
                          Remark,
                          Status,
                          Link,
                          Crcy as Currency,
                          [Wage Type Long Text],
                          [Start pro.] as [Project start date],
                          [End date] as [Project end date],
                          Date,
                          [Created on],
                          [Changed on],
                          [Project time],
                          [T&D trv. time],
                          [Project time1],
                          [Planned hours],
                          FTE,
                          WTE,
                          Resource,
                          Cost,
                          Amount,
                          Area,
                          [Time Category],
                          [Time category group],
                          Month,
                          If([Time category group]= 'Billable', [Project time],
                          If([Time category group]= 'Productive', [Project time])) as BillProd

                          FROM
                          [C:\Documents and Settings\jnewman8\My Documents\Qlikview\FS Utilisation\Consolidated Data.xlsm]
                          (ooxml, embedded labels, table is [Utilisation]);


                          Join (Projects)
                          LOAD DISTINCT
                          EmployeeKey,
                          Month,
                          sum(BillProd)/sum([Project time]) AS PercentageBP
                          Resident Projects
                          GROUP BY EmployeeKey, Month;


                          RAG:
                          LOAD DISTINCT
                          EmployeeKey,
                          If(PercentageBP < 0.49999,1,0) as Red
                          Resident Projects;

                          But it does not give me the answers I want
                          Thanks for your help
                          Bryant