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