topic Re: Mark new data in QlikView

Mark new data

Thu, 19 Jun 2014 09:18:19 GMT

Hi,

I have a question.

I am currently building an order collector tool.

The orders are written down in Excel and then collected in Qlikview.

The client now is interested in seeing which row are added since the last reload(reload is 4 times a day).

How would i be able to mark those new rows?

Thanks for any help and ideas. !

Re: Mark new data

swuehl — Thu, 19 Jun 2014 10:12:20 GMT

How do you collect the data in your application?

For example, do you use an incremental load approach or a partial load?

You can think about creating a flag added to your table indicating the new records or an additional table linked to the records key field with that flag, you just need to ensure that the field / table is reset before the next load.

Or create just an additional field with the load time stamp or an load incremental identifier, and filter the records for the max value in your front end to show latest records.

Re: Mark new data

maxgro — Thu, 19 Jun 2014 10:12:45 GMT

store order in a qvd file

when you reload

first load qvd

then load from excel only new record (=record not loaded from qvd) and add a flag for these records

store in qvd (without the flag)

repeat at every reload

Re: Mark new data

Thu, 19 Jun 2014 13:22:00 GMT

I like this idea.

I just dont understand how I can load only new records from Excel, can you explain that part to me?

Re: Mark new data

Thu, 19 Jun 2014 13:24:25 GMT

I normally do a full reload. I get the idea of adding some identifier to mark new data. i just need to figure out how i can mark the New data.

Re: Mark new data

juleshartley — Thu, 19 Jun 2014 13:27:21 GMT

See Steve D blog here:

http://www.quickintelligence.co.uk/qlikview-incremental-load/

There are a few different ways of doing incremental loads, depending on the data you have.

Re: Mark new data

swuehl — Thu, 19 Jun 2014 13:40:37 GMT

If you do a full reload and the original data does not have a kind of indicator flagging New records itself (like a modified timestamp field), how do you tell which records are new? Do you have a primary key and only add data, not updating ones? Then you can look into something like a incremental load, storing historic data into a qvd and loading then the historic data first and the new with a WHERE clause checking your key on existence

LOAD Key, ..., 'OLD' as Status FROM Historic.qvd (qvd);

LOAD Key,... 'New' as Status FROM ...

WHERE NOT EXISTS( Key);

edit: See also the incremental load sample in the QV cookbook:

http://robwunderlich.com/downloads/cookbook_v10c_PE.zip

Re: Mark new data

Thu, 19 Jun 2014 13:54:16 GMT

All the data is Excel Data, no primary keys are used

Re: Mark new data

avkeep01 — Thu, 19 Jun 2014 14:25:12 GMT

Hi Nard,

Does the Excel file have a unique number? For example a rownumber of unique ID? Then you can create something like this:

// Check if QVDFile exists

IF FILESIZE('<QVDfile>') > 0 THEN

MaxTable:

LOAD MAX(ID) AS Flag FROM <QVDfile>;

LET vMaxID = PEEK('Flag',0,'MaxTable');

Data:

LOAD

FROM <excelfile>

WHERE ID > $(vMaxID);

CONCATENATE (Data) LOAD

FROM <QVDfile>

ELSE

Data:

LOAD

FROM <excelfile>;

END IF

STORE Data into <QVDfile>;

Re: Mark new data

maxgro — Thu, 19 Jun 2014 14:33:24 GMT

with not exists

as swuehl suggests

Re: Mark new data

Tue, 24 Jun 2014 10:48:13 GMT

Sadly there are no unique numbers in the excel file.

Re: Mark new data

Tue, 24 Jun 2014 13:03:54 GMT

Eventually i found the correct solution. I created a key by combining all the fields as 1 field for key.

and then using your suggestions to create the incremental load.