5 Replies Latest reply: Jan 31, 2013 11:46 AM by lauranunez RSS

    Script Resident Load Issue

      Hi,

       

      I'm trying to load in a script as below but I'm getting a "Field not found" error. Any ideas what I'm doing wrong?

       

      Let vToday = Today();
      Let vAYearAgo = Date(AddYears(Today(),-1),'YYYY-MM-DD');
      
      StaffRecord:
      LOAD 'Yearly' As [Frequency],
                if(not Status = '',1,0) as EntryExists,
                if(Status = 'Completed',1,0) as Completed,
                if(Status = 'Requested',1,0) as Booked,
                if(Status = 'Did Not Attend',1,0) as DNA,
                if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                [Staff Group],
                [Date]
      FROM
      [SPREADSHEET.xlsx]
      (ooxml, embedded labels, header is 1 lines)
      where RowNo() < 1000;
      
      DNAReport:
      First 3 
      LOAD [Staff Group],
                Sum([DNA])  as [TotalDNA]
      Resident StaffRecord 
      where [Date] > $(#vAYearAgo) and [Date] < $(#vToday)
      group by [Staff Group]
      order by [TotalDNA] ASC;
      

       

       

      Sorry that it's a very specific query, but I think it's my knowledge of the resident load that's causing me problems. That, or the order/group by.

       

      Cheers!

       

      EDIT: I'm now certain it's to do with the order by, as it works without it in. Is there any way to order by the fields, or will I have to do the query on the chart itself to only select the top 3?

        • Re: Script Resident Load Issue
          Henric Cronström

          The filed TotalDNA does not exist in the table StaffRecord.

           

          In QlikVIew 11 you can set a chart in the layout to show only the top 3.

           

          HIC

          • Re: Script Resident Load Issue
            Dave Riley

            You can do this by a 2-step approach, first GROUP BY the field, then inner join itself by the ORDER BY ...

             

            DNAReport:

            Load [Staff Group], Sum(DNA) as TotalDNA

            Resident StaffRecord

            group by [Staff Group];

             

            inner join

             

            first 3 load [Staff Group], TotalDNA

            resident DNAReport

            order by TotalDNA;

             

            Another way is to use an ODBC connection to your excel file where you can group by and order by in one command ...

             

            ODBC CONNECT32 TO [Excel Files;DBQ=StaffGroup.xls];

            SQL SELECT top 3 [Staff Group], Sum(DNA) as TotalDNA

            FROM `StaffGroup`.`Sheet1$`

            group by [Staff Group]

            order by Sum(DNA);

             

             

            flipside

              • Re: Script Resident Load Issue
                Marina Alvarez

                first 3 is to load the 3 first rows of this load?

                  • Re: Script Resident Load Issue
                    Dave Riley

                    Yes, the "first 3" will bring back the top 3 rows in ascending order of TotalDNA as per the original requirement.  When this is inner-joined back to the same table it effectively drops the data not needed.

                      • Re: Script Resident Load Issue

                        I'm having the same problem, but in the answer they forgot the filter:

                        where [Date] > $(#vAYearAgo) and [Date] < $(#vToday).

                         

                        This is my script:

                        LOAD     `IT_ICODE` as [Codigo de Producto],

                            `IT_WHCODE` as [Codigo Almacen],

                            `IT_LOCAL` as Sucursal,

                            `IT_QTY_SHP` as QtySales;

                        SQL SELECT *

                        FROM `C:\USERS\LAURA NU\DOCUMENTS\COMETA\QLIK VIEW\COMETA\DBF`\itransd;

                         

                        QtyVendida:

                        load [Codigo de Producto],

                             sum(QtySales) as QtyVendida2

                        resident Ventas

                        where QtySales >0;

                         

                        And is very important for me to filter with positive values.