Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madhukar_putta
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
tresesco
MVP
MVP

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

Not applicable

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?

Anonymous
Not applicable

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>

madhukar_putta
Contributor III
Contributor III
Author

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;

andrewpettit
Partner - Creator
Partner - Creator

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.