5 Replies Latest reply: Jul 22, 2011 2:44 AM by MMuhonen RSS

    An empty table causes loading to fail

      Hi,

       

      I have this serious problem with an empty table. The data comes in csv-file and it is empty. When loading data (in ETL-process) in and concatenating it with existing qvd-file "the loading" fails. The code that I'm using is here:

       

      // -


      // Load data from source

      // -


      Temporary:

      LOAD date(@1, 'D-M-YYYY') as transaction_date,

           right(@2, 3) as work_number,

           left(@2, 4) as project_id,

           @3 as line_id,

           @4 as qty,

           @5 as area,

           @6 as company_id,

           'material transactions' as source_system_id

      FROM

           $(Data)\dwmtran.csv (txt, codepage is 1252, no labels, delimiter is ';', msq, header is 9 lines);

      // -


      // Sort the data by date

      // -


      Fact:

      NoConcatenate

      LOAD * RESIDENT Temporary ORDER BY transaction_date; // CHANGE DATE

       

      let maxQVDDay = Peek('transaction_date', 0, 'Fact'); // CHANGE THE COLUMN NAME

      if $(maxQVDDay) <= "0" then

           let maxQVDDay = DATE((TODAY() + 1));

      end if

       

      let companyID = Peek('company_id', 0, 'Fact');

       

      // -


      // Join data from QVD

      // -


      CONCATENATE

      LOAD *

      FROM

           $(qvdData)\fact_data.qvd (qvd)

      WHERE

           // Load history rows for this company only and this source

           (transaction_date < $(maxQVDDay) and company_id = $(companyID) and source_system_id = 'material transactions') // CHANGE THIS

           // Load all rows for all other companies

           OR (company_id <> $(companyID))

           // Load all rows for all other sources

           OR (source_system_id <> 'material transactions');

       

       

      // -


      // Store data back to QVD

      // -


      STORE Fact INTO $(qvdData)\fact_data.qvd;

      DROP TABLE Fact;

      DROP TABLE Temporary;

       

      The problem is that when searching the peek date QlikView returns  for maxQVDdate "NULL" value, which although is right (right?). The if-statement seems to be the problem but I don't know how to fix it. I want to loading to proceed even though the csv-file is empty. Do you have any idea how to fix this problem?

       

      -Mikko

        • An empty table causes loading to fail
          Stefan Wühl

          Hi Mikko,

           

          I think you should test for NULL using isNull() function.

           

          Stefan

            • An empty table causes loading to fail

              Hi swuehl,

               

              and thanks for reply.

               

              I have tried to put OR-statement into if-statement (just trying something ) as like this:

               

              if $(maxQVDDay) <= "0"  OR $(maxQVDDay) = Null() then

                   let maxQVDDay = DATE((TODAY() + 1));

              end if

               

              And as you can guess, it did not work. How your statement "isNull(exp)" would help (and where to put it)? The csv-file is now empty but in future there will be data.

               

              -Mikko

                • An empty table causes loading to fail
                  Erich Shiino

                  Hi,

                  Maybe you can use filesize() to test the size before trying to concatenate it:

                   

                  filesize( 'xyz.xls' )

                   

                  Something like:

                  if (  filesize( 'xyz.xls' ) > ??? (you will have  to try some value that makes sense in your case ) ) then

                   

                  Temporary:

                   

                  LOAD date(@1, 'D-M-YYYY') as transaction_date, ..

                   

                   

                  end if

                   

                  Hope this helps,

                   

                  Erich

                   

                   

                  • Re: An empty table causes loading to fail
                    Stefan Wühl

                    Hi Mikko,

                     

                    I was thinking of something like

                     

                     

                    if isNull($(maxQVDDay))  then

                         let maxQVDDay = DATE((TODAY() + 1));

                    end if

                     

                     

                     

                    Alternatively, you could check the number of rows read with function NoOfRows('tablename')

                     

                    LET numRowsFact = NoOfRows('Fact');

                     

                    if  $(numRowsFact) = 0  then

                         let maxQVDDay = DATE((TODAY() + 1));

                    end if

                     

                    I think this should work better than filesize.

                     

                    Regards,

                    Stefan

                      • Re: An empty table causes loading to fail

                        Hi and thank you for helpful answers,

                         

                        I tried IsNull(exp) -statement but I faced another problem which comes next when joining the data with existing qvd file.

                         

                        Checking the number of rows helps, although I have to think the case when the table is not empty; after checking the number of rows and if not 0, then continue with searching the smallest date.

                         

                        The code would be something like this:

                         

                        LET numRowsFact = NoOfRows('Fact');

                         

                        if  $(numRowsFact) = 0  then

                             let maxQVDDay = DATE((TODAY() + 1));

                        ELSE

                        let maxQVDDay = Peek('transaction_date', 0, 'Fact'); // CHANGE THE COLUMN NAME

                        if $(maxQVDDay) <= "0" then

                             let maxQVDDay = DATE((TODAY() + 1));

                         

                        end if

                         

                        Same thing has to be made to company_id otherwise script loading fails again.

                         

                        -Mikko