Qvd's store less data

Thu, 17 May 2012 12:47:17 GMT

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 — Thu, 17 May 2012 13:41:46 GMT

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

topic Re: Qvd's store less data in QlikView

Qvd's store less data

Re: Qvd's store less data