Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Qvd's store less data

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;

Tags (4)
1 Reply
flipside
Valued Contributor II

Re: Qvd's store less data

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

Community Browser