Mark new data

[#1]

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

[swuehl · Accepted solution]

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.

[maxgro]

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

[#4]

I like this idea.

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

[#5]

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.

[juleshartley]

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.

[swuehl]

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

[#8]

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

[avkeep01]

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

[maxgro]

with not exists

as swuehl suggests