topic Another Incremental Question - Append Only in QlikView

Another Incremental Question - Append Only

Fri, 15 Apr 2011 12:04:16 GMT

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.

I have the following code:


"CARTONSCANNING":
LOAD
cs.recordno,
date(cs.scandate),
time(cs.scantime),
cs.line,
cs.va,
cs.pk,
cs.sz,
cs.gr,
cs.nc,
cs.fc,
cs.pu,
cs.pn,
cs.tk,
cs.co,
cs.oz
FROM $(StorageFolder)CARTONSCANNING.QVD (QVD)
WHERE cs.recordno <= '530000';

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";

Another Incremental Question - Append Only

vgutkovsky — Fri, 15 Apr 2011 23:38:24 GMT

Ruan,

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).

Cheers,

Another Incremental Question - Append Only

Mon, 18 Apr 2011 09:03:05 GMT

thanx in advance, i will try this sometime during the week and give feedback,.