1 Reply Latest reply: May 17, 2012 9:44 AM by flipside RSS

    Qvd's store less data

    Tiu Adrian

      Hi, i'm new in Qlikview and i have a question. Is it possible that when you store data into qvd files qlikview will store less rows?

      I need to load a big amount of data (aprox. 32000000 rows) from a sql table. Because my connection to database is resetting i tryed to make a loading script that will store 1000000 rows at the time and if my connection will be resetted i'll have som of my data in the qvd file.

      My problem is that when i load the data from qvd to concatenate to the result of new selection i get only 300, mabe 400 rows from qvd.

       

      here is the script:

       

       

      LET vQvdCardsExists = if(FileSize('$(vQVDFolder)' & '\' & '$(vPrefix)' & 'Cards.QVD') > 0, -1, 0);
      
          TempCardsE:
          NoConcatenate
          LOAD *;
          SQL Select max(CRD_ID) as NumberE from $(vDatabase).dbo.CRD where TRA_TRA_ID is not null;
      
          LET vCardCountEnd = Peek('NumberE');
      
          if $(vQvdCardsExists) then
      
              TempCardsB:
              NoConcatenate
              LOAD max(CRD_ID) as NumberB from $(vQVDFolder)\$(vPrefix)Cards.QVD (qvd);
      
              LET vCardCountBegin = Peek('NumberB');
      
          ELSE
      
              LET vCardCountBegin = 96071;
      
          ENDIF
      
          FOR i = $(vCardCountBegin) to $(vCardCountEnd) step 1000000
      
          LET vQvdCardsExists = if(FileSize('$(vQVDFolder)' & '\' & '$(vPrefix)' & 'Cards.QVD') > 0, -1, 0);
      
          Cards:
          LOAD *;
          SQL Select * from $(vDatabase).dbo.CRD where TRA_TRA_ID is not null and CRD_ID > $(i) and CRD_ID < ($(i) + 1000000);
      
          if $(vQvdCardsExists) then
              Concatenate(Cards)
              LOAD * from $(vQVDFolder)\$(vPrefix)Cards.QVD;
          ENDIF
      
          STORE Cards into $(vQVDFolder)\$(vPrefix)Cards.QVD (qvd);
              DROP Table Cards;
      
          NEXT i
      
      DROP Tables TempCardsE, TempCardsB;
      
        • Re: Qvd's store less data
          flipside

          Hi adriantiu,

           

          Welcome to Qlikview!!

           

          I think the issue in your script is in the QVD load within the loop, you are missing the (qvd) syntax ...

           

          if $(vQvdCardsExists) then

                  Concatenate(Cards)

                  LOAD * from $(vQVDFolder)\$(vPrefix)Cards.QVD (qvd);

          ENDIF

           

          You might also need to change the where clause to CRD_ID >= $(i)

           

          There may be a better way of doing this, though, using incremental load which will reduce the loop to something like ...

           

          FOR i = $(vCardCountBegin) to $(vCardCountEnd) step 1000000

           

              Cards:
                Buffer (Incremental)  LOAD *;
                SQL Select * from $(vDatabase).dbo.CRD where TRA_TRA_ID is not null and CRD_ID > $(i) and CRD_ID < ($(i) + 1000000);


              STORE Cards into Cards.QVD (qvd);
             
          NEXT i

           

          flipside