Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Regarding QVD size after incremental reload.


Hello all,

In my application (QVD Extractor), I have a QVD with size 91 MB. This QVD is reloading every day at once. This is complete reload. Not like Incremental reload or partial reload.

Now I changed the application with incremental reload. Its working fine but my QVD size went to 257 MB size instead of apporx. 91 MB . I dont know the reason why it went to that much size.

Full Reload = Size of QVD = 91 MB

Incremental Reload = Size of QVD = 257 MB.

Please let me know the reason and provide me what should i do further to decrease the size of QVD. Thanks in advance.

5 Replies
Highlighted
MVP
MVP

Re: Regarding QVD size after incremental reload.

Probably your incremental load script has not been proper. Check if the data after incremental load is getting duplicated or not. 

Highlighted
Not applicable

Re: Regarding QVD size after incremental reload.

Hi,

Check the steps which you have followed to do incremental load. Is it anyware the load statement gets loaded double the records? Did you used CONCATENATE to merge the old records with newly loaded records?

Highlighted
Creator II
Creator II

Re: Regarding QVD size after incremental reload.

HI,

hope this helps you. It works fine for me

LOAD <YOUR LAST RECORDS>

FROM <YOUR DB TABLE>

...

CONCATENATE

LOAD <YOUR FIELDS>

FROM  <YOUR QVD TABLE> (qvd)

WHERE NOT EXISTS (<YOUR PRIMARY KEY>)

STORE <YOUR FINAL TABLE>

Highlighted
Contributor III
Contributor III

Re: Regarding QVD size after incremental reload.

Hello,

Pls find the code below. Sorry for late reply. I am out of station.

Pls tell me if any thing wrong.

CODE :-

SET vQvdFile='TB_SHIPP_OPR_DTL.qvd';
SET vTableName='TB_SHIPP_OPR_DTL';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN
maxdateTab:
LOAD max(CRE_DT) as maxdate
FROM $(vQvdFile) (qvd);
LET vIncrementalExpression = 'WHERE CRE_DT >=' & fieldValue('maxdate', 1);
DROP table maxdateTab;
ELSE
LET vIncrementalExpression = '';

END IF

$(vTableName):

LOAD "DEPOT_CD",

    "OPR_CMP_UPD_DT_TM",

    "OPR_CMP_SRL_NO",

    "SHIPP_OPR_RECD_CD",

    DEPOT_CD&OPR_CMP_UPD_DT_TM&OPR_CMP_SRL_NO&SHIPP_OPR_RECD_CD as PK,

    "PICK_CYL_NO",

    "SHIP_PART_NO",

    "PICK_INS_QTY",

    "CUST_CD",

    "TOPAS_ORD_TYPE",

    "UPD_OPR_ID",

    "CRE_DT"

;

SQL SELECT *

FROM MA0USER00."TB_SHIPP_OPR_DTL"

$(vIncrementalExpression);

Directory;

IF $(vQvdExists) THEN

CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd)
WHERE NOT exists(PK);

END IF

INNER JOIN ($(vTableName))

LOAD

DEPOT_CD&OPR_CMP_UPD_DT_TM&OPR_CMP_SRL_NO&SHIPP_OPR_RECD_CD as PK

;

SQL SELECT *

FROM MA0USER00."TB_SHIPP_OPR_DTL";

STORE TB_SHIPP_OPR_DTL into E:\Qlik2GTOPAS\WorkArea\TB_SHIPP_OPR_DTL.qvd;

Exit Script;

Highlighted
Partner
Partner

Re: Regarding QVD size after incremental reload.

Not sure if any of these thoughts will solve the problem but I hope they are useful thoughts none the less.

1) Have you changed the columns or the definition of the PK at any point?  I find its important when doing incremental loads to always build in a mechanism to allow you to force a full reload, granted you could simply delete your QVD file and that would work.  If you added or removed fields along the way and did not do a full reload then you may have extra unneeded values in your older data and that could cause an unexplained size differences if comparing an incremental file to a non-incremental file that was recently loaded with only the current field list.

2) I don't recommend your method for creating your PK value.  One long string values are not efficient for storage or processing and an autonumber or autonumberhash128 could potentially produce a better more compact key.  Second you do not appear to be separating the codes and values that make up the key this could cause problems in some cases for instance 101 concatenated with 1 and 10 concatenated with 11 both produce a value of 1011.  This could cause unintended effects in your load process and cause data to erroneously duplicate when making joins, or erroneously be filtered out when using conditions like the WHERE Exists(...).

3) Have you looked at the meta-data on the two QVDs to compare the differences? That may shed some light on where the differences are coming from.