Im trying to solve a incremental load problem i just cant solve, i use a QVD file that stores a certain mysql table. now i want to collect the new entries in the mysql table and append it to my QVD but i cant figure how to make it look at the QVD max (recordno) and only add mysql data after that record. at the moment i tested a manual figure of 530000 and it working. any help greatly appreciated.
JOIN LOAD recordno as cs.recordno, date(scandate) as cs.scandate, date(today()) as cs.today, scantime as cs.scantime, line as cs.line, va as cs.va, pk as cs.pk, sz as cs.sz, gr as cs.gr, nc as cs.nc, fc as cs.fc, pu as cs.pu, pn as cs.pn, tk as cs.tk, co as cs.co, oz as cs.oz; SQL SELECT * FROM vitrax_2011.bcinputs WHERE recordno > '530000';
STORE "CARTONSCANNING" INTO $(StorageFolder)CARTONSCANNINGTEMP.QVD (QVD); DROP TABLE "CARTONSCANNING";
The easiest way is to use the peek() function but that will only work if your QVD is stored with a sortorder by recordno or you do a GROUP BY. Then you can create the following variable after your first table load: LET vMaxRecord = peek('cs.recordno',-1,CARTONSCANNING); You can then use this variable in your SQL load by check < $(vMaxRecord). If your QVD is not stored in a sorted way, you will need to load it from QVD. Then create a temporary table as follows:
temp_table: NOCONCATENATE LOAD * RESIDENT CARTONSCANNING ORDER BY cs.recordno;
The peek variable would then reference the temp_table. As soon as you set the variable you can drop the temp_table.
Another alternative, if you don't want to sort it, is to do a GROUP BY. Add the following field to your first table load: 1 as temp_counter. Then create your GROUP BY table:
temp_table: LOAD temp_counter, max(cs.recordno) as maxrecord RESIDENT CARTONSCANNING GROUP BY temp_counter;
LET vMaxRecord = peek('maxrecord',1,temp_table);
Either way, you can then use the vMaxRecord variable in your second load. By the way, your second load should be a CONCATENATE instead of an outer join (it's faster).
