topic Re: Incremental Load in QlikView

Incremental Load

Tue, 25 Mar 2014 19:13:28 GMT

Hi Folks,

I have the below scenario:

A text file contains 10 pincodes, at the first round I shall read them all and get the longitude and latitude and store it in a QVD file.

I run the script again but this time 5 more pincodes are added to the source text file. I wish to calculate the longitude and latitude only for newly loaded ones and store them back in existing QVD file.

How do I proceed with this?

Thank you.

Re: Incremental Load

whiteline — Tue, 25 Mar 2014 20:46:35 GMT

Hi.

You can use exists function (see help and search the community) to load only those pincodes that is not present in the list loaded from the existing qvd.

Then process the new ones, concatenate and store (overwrite) the qvd.

Re: Incremental Load

Tue, 25 Mar 2014 20:52:09 GMT

Hi Pramod, this is a typical 'Insert' incremental load.

Re: Incremental Load

fkeuroglian — Tue, 25 Mar 2014 21:19:46 GMT

Hi, you have to use the exist function and day by day add only the data that NOT EXISTS in the first (original) information

you will have a table that woul be increase day by day by the data that not exists in the oldest table

good luck

Fernando

Re: Incremental Load

Wed, 26 Mar 2014 19:26:50 GMT

Thank you for your help guys.

I have now the below scenario:

I have a temp.qvd file which loads has the data pincode, lat, long and coordinates. Say I have 10 pincodes of which I get the lat and long for only 3 of them when I pass them to the api of Open Street Map/Google. I store them in temp.qvd accrdingly. Now I have 7 records which in temp.qvd which have long and lat as nulls(the api could not locate the points). After some months, the api has updated the points on the map. Now i wish to use the same temp.qvd file to check of long and lat for the pincodes which dont have.

How do I read from a file and update the rows of pincode with respective long and lat without inserting a duplicated records at the end?

Hope I was clear.

Thank you.

Re: Incremental Load

maxgro — Wed, 26 Mar 2014 19:59:07 GMT

- read from temp.qvd the record with missing lat

temp:

load

id,

lat,

lon,

......

from temp.qvd (qvd)

where len(trim(lat))=0 // or isnull(lat)

- calculate lat and long with api starting from temp

- read from temp.qvd the record with lat and concatenate

concatenate (temp)

load *

from temp.qvd (qvd)

where len(trim(lat))>0;

- store temp

store A into temp.qvd (qvd);