5 Replies Latest reply: Apr 16, 2012 9:50 AM by etay elazar RSS

    Any idea why this wont work?

       

      Cant get script below to work :-

       

      Source_Data:

      LOAD [Report No] as rpt_num,

      capitalize([Requested by]) as req_by,

      capitalize(Department) as Dept,

      date([Date Requested]) as date_req

      FROM

      [D:\Qlik View\Reports\TESTING\Paul\Mi Report for Esther\WORK LIST.xls]

      (biff, embedded labels, table is [Completed Ad-hoc jobs$])

      where today()-date([Date Requested])<=365

      ;

      Data_sort:

      LOAD   rpt_num,

      count(rpt_num) as Report_no

      resident Source_Data

      group by req_by

      ;

       

       

      Am trying to run a report to count business reports and count how many times an individual makes a request.

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Any idea why this wont work?
          Christophe Brault

          Hi,

           

          I think the problem is in your table Data_sort. You must use agregation with GROUP BY.

           

          Try to add Only(rpt_num) as rpt_num).

           

          If this doesn't work, can you give more detail of your issue ? Error in script or not working as you wish ?

            • Any idea why this wont work?

              I tried this :-

              Source_Data:

              LOAD [Report No],

              capitalize([Requested by]) as req_by,

              capitalize(Department) as Dept,

              date([Date Requested]) as date_req

              FROM

              [D:\Qlik View\Reports\TESTING\Paul\Mi Report for Esther\WORK LIST.xls]

              (biff, embedded labels, table is [Completed Ad-hoc jobs$])

              where today()-date([Date Requested])<=365

              ;

              Data_sort:

              LOAD   [Report No],

              count([Report No]) as Report_no

              resident Source_Data

              group by req_by

               

              and got error :- qview error.JPG

               

                • Any idea why this wont work?
                  Celambarasan Adhimulam

                  Hi,

                       Check with this

                      

                  Source_Data:

                  LOAD [Report No],

                  capitalize([Requested by]) as req_by,

                  capitalize(Department) as Dept,

                  date([Date Requested]) as date_req

                  FROM

                  [D:\Qlik View\Reports\TESTING\Paul\Mi Report for Esther\WORK LIST.xls]

                  (biff, embedded labels, table is [Completed Ad-hoc jobs$])

                  where today()-date([Date Requested])<=365

                  ;

                  Data_sort:

                  LOAD  req_by,

                             [Report No],

                                 count([Report No]) as Report_no

                  resident Source_Data

                  group by req_by,[Report No];

                   

                  Celambarasan

                    • Any idea why this wont work?
                      Celambarasan Adhimulam

                      Hi,

                            You can only able to use the Field names directly(without aggregate functions) when its is specified in a group by clause otherwise you need to use inside Aggregate Functions.

                       

                      Hope you trying to achieve Number of report numbers in request by category for that check the script below

                      Data_sort:

                      LOAD   req_by,

                                     count([Report No]) as Report_no

                      resident Source_Data

                      group by req_by;

                       

                      Celambarasan

                • Any idea why this wont work?
                  etay elazar

                  Hi there

                  Celambarasan is correct.

                  You can not count a field and present it explicitly. The count would be 1 pre line.

                  When using GroupBy clause all fields must be the result of an aggregation function, or present in the group by statement. That is the error you are getting.

                  What were you attempting to accomplish ??