4 Replies Latest reply: Jul 30, 2012 7:33 AM by Joe Kirwan RSS

    Why are these records loading

    Joe Kirwan

      I have the following script:

       

       

      LOAD Order_Num  ,
           73050
      as Start,
           73050
      as InvDate,
         
      [Book Value] as InvAmt,
         
      [Book Value] as TtlInv,
           'BkVal'
      as Type
      FROM
      FILE

      where [Book Value] <> 0 and len(Date) =0;

       

      The idea is that I allocate the date 73050 (31/12/2099) in instances where there is a value in [Book Value] field but no Date in the Date field..

       

      As it happens, there are no records with len(Date) = 0.

       

      But the following records are appearing on my table:

       

      Order_NumStartInvAmtInvDateTypeTtlInv
      31/12/2099 31/12/2099BkVal

       

      I don't want these records to appear.

       

      What am I doing wrong?

       

      Ta

       

      Joe

       

       

       

        • Re: Why are these records loading
          Miguel Angel Baeyens de Arce

          Hi Joe,

           

          Probably obvious but are you sure those "Date" values are null values (length = 0)? I mean, they might be blanks or some kind of spaces. The following should avoid that, removing blanks around the values in the cell:

           

          WHERE [Book Value] <> 0 and Len(Trim(Date)) = 0;
          

           

          Hope that helps.

           

          Miguel

            • Re: Why are these records loading
              Joe Kirwan

              Hi Miguel

               

              That didn't work for me - but it might be pointing toward my problem.

               

              My source is a spreadsheet file. I think it might be loading these records for blank rows at the end of my data.

               

              I have added a condition to my  where clause:

               

              where len(Order_Num)>0, and it seems to get around the problem.

               

              But I suspect I may have records in the future without a value in Order_Num; and I WILL want to load these fields (but they will be excuded by tha above condition).

               

              Is there a way to ignore blank rows at the end of my data in a spreadsheet?

               

              Or is this my problem in the first place?

               

              Regards

               

              Joe

                • Re: Why are these records loading
                  Miguel Angel Baeyens de Arce

                  Hi Joe,

                   

                  I'd say that there is no easy way to control that. Meaning that assuming that today you have 20 rows worth of data, plus four with blanks and another two with data as well, you would need to control that if the length of the whole row (summing up the Len(Trim()) of each column) equals to zero, then do not load them, otherwise, load them.

                   

                  This would allow you to perform unattended reloads irrespective the number of rows within with all blanks.

                   

                  You might try to load the spreadsheets using and ODBC driver, instead of the table files wizard in QlikView, and control there which fields are key and so which rows should be always loaded, regardless the number of Order_Num or so.

                   

                  The best way to get that around is doint some cleanup in the source, if that's possible.

                   

                  Hope that gives you a better idea.

                   

                  Miguel