Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Probably your incremental load script has not been proper. Check if the data after incremental load is getting duplicated or not.
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?
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>
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;
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.