topic Re: update with new time stamp. in QlikView

update with new time stamp.

userid128223 — Wed, 12 Feb 2014 22:02:47 GMT

Hi Everyone.

I have a SOURCE qvd with following type of records.

ID Name Amount update_date

123 james $300 Feb-01-2014

127 John $234 Feb-01-2014

547 Alex $555 Feb-02-2014

New file from sales comes in daily basis with new and corrected records. Therefore I want to update any corrected records and append new records in above qvd.

for example today's sales file contains old & corrected records with update date.

ID Name Amount update_date

123 james $300 Feb-01-2014 // gets dropped as new entry from same ID but new date exist

123 james $500 Feb-03-2014

127 John $234 Feb-01-2014

547 Alex $555 Feb-02-2014

432 Judy $800 Feb-10-2014 // gets added as new entry.

My source should get updated with all the new records + all the updated records with latest update date.

Final results.

ID Name Amount update_date

123 james $500 Feb-03-2014

127 John $234 Feb-01-2014

547 Alex $555 Feb-02-2014

432 Judy $800 Feb-10-2014

thanks

Re: update with new time stamp.

maxgro — Wed, 12 Feb 2014 22:22:17 GMT

a simple way could be

1) concatenate SOURCE and DAILY files in one table

2) read that table order by ID and date desc and filter using the peek function (keep only the record where ID change)

Re: update with new time stamp.

userid128223 — Wed, 12 Feb 2014 22:25:03 GMT

Can you please share examples

Re: update with new time stamp.

maxgro — Wed, 12 Feb 2014 22:32:36 GMT

SCRIPT

Source:

LOAD * INLINE [

ID, Name, Amount, update_date

123, james, 300 , feb-01-2014

127, John , 234 , feb-01-2014

547, Alex , 555 , feb-02-2014

];

Concatenate

LOAD * INLINE [

ID , Name , Amount , update_date

123 , james , 300 , feb-01-2014

123 , james , 500 , feb-03-2014

127 , John , 234 , feb-01-2014

547 , Alex , 555 , feb-02-2014

432 ,Judy , 800 , feb-10-2014

];

Final:

NoConcatenate load

Resident Source

where Peek(ID) <> ID

order by ID, update_date desc;

drop table Source;

RESULT

ID	Name	Amount	update_date
123	james	500	feb-03-2014
127	John	234	feb-01-2014
432	Judy	800	feb-10-2014
547	Alex	555	feb-02-2014

Re: update with new time stamp.

swuehl — Wed, 12 Feb 2014 22:32:58 GMT

So you get a daily delta file, right?

Like for today:

DATA:

LOAD * INLINE [

ID, Name, Amount, update_date

123, james, $500, Feb-03-2014

432, Judy, $800, Feb-10-2014

];

So, load the new data first, then concatenate your old data from the source.qvd, but exclude the existing ID from the new DATA:

LOAD * INLINE [

ID, Name, Amount , update_date

123, james, $300, Feb-01-2014

127, John, $234, Feb-01-2014

547, Alex, $555, Feb-02-2014

] where not exists (ID);

Then store the resulting table back as to your source.qvd.

[Instead of the inline table for demonstration, use the qvd and your DB source as data input, i.e. replace the INLINE loads with FROM loads]

Re: update with new time stamp.

Wed, 12 Feb 2014 22:35:00 GMT

Hi,

If I understand ... you can try "Where not exists()", somenthing like this:

Sales

Load

ID,

Name,

Amount,

update_date

from newinformation;

Load

ID,

Name,

Amount,

update_date

from information.qvd // old information

where not exists(ID,ID);

Above example keep the actual information and delete old.

Best regards

Re: update with new time stamp.

userid128223 — Wed, 12 Feb 2014 22:37:27 GMT

Swuehl

I have tried doing what you did. however since daily file contain 1 records with 2 different time stamp. hence it will fetch both in merge file.

so Daily has

ID, Name, Amount, update_date

123 , james , 300 , Feb-01-2014

123, james, $500, Feb-03-2014

432, Judy, $800, Feb-10-2014

];

so if you tried where not exist (ID) it will get both records for James.

Re: update with new time stamp.

Wed, 12 Feb 2014 22:37:30 GMT

hope this will helps you

Table:

Load *

from QVD;

Concatenate

Load *

from source

where not exist (primary key);

Re: update with new time stamp.

maxgro — Wed, 12 Feb 2014 22:39:16 GMT

I supposed he has duplicated ID (123) also in daily data

Re: update with new time stamp.

userid128223 — Wed, 12 Feb 2014 22:42:21 GMT

yes you are correct. daily data comes with duplicate ID and new info including updated time date stamp.

Re: update with new time stamp.

swuehl — Wed, 12 Feb 2014 22:44:48 GMT

Either try to load your daily data sorted by ID and Date desc, then use the where not exists (ID) clause,

or load your daily data:

DAILY:

LOAD * INLINE [

ID, Name, Amount, update_date

123, james, $300 , Feb-01-2014

123, james, $500 , Feb-03-2014

127, John , $234 , Feb-01-2014

547, Alex , $555 , Feb-02-2014

432, Judy , $800 , Feb-10-2014

];

and then

LOAD *, ID as TmpID Resident DAILY where not exists(TmpID, ID) order by ID, update_date desc ;

drop table DAILY;

drop field TmpID;

Re: update with new time stamp.

Wed, 12 Feb 2014 22:54:34 GMT

Ufff

I'm not sure but you can try using an applymap with a key (ID&'_'&update-date) with the old information and put a flag to remove this rows:

Map_Remove:

Mapping

Load
ID & '_' & update-date as Remove,

0 as flagtoremove

from oldinformation;

Sales:

Load

ID,

Name,

Amount,

update_date,

applymap('Map_remove', ID & '_' & update-date,1) as flag

from newinformation

where applymap('Map_remove', ID & '_' & update-date,1) = 0;

Above keep only actual information delete old information in second source.

Best regards.

Re: update with new time stamp.

userid128223 — Wed, 12 Feb 2014 23:04:04 GMT

Hi Swuehl

I will try this and let you know, however can you please explain this code.

LOAD *, ID as TmpID Resident DAILY where not exists(TmpID, ID) order by ID, update_date desc ;

how does it work, whats the logic.

thanks

Re: update with new time stamp.

userid128223 — Thu, 13 Feb 2014 02:31:13 GMT

HI Swuehl

so you are saying if I sort the data by ID and Date desc and then use not exist it will only pick the first instance of record and that way get the latest update.

second question how do you sort by ID and Date desc

Re: update with new time stamp.

userid128223 — Thu, 13 Feb 2014 14:38:54 GMT

Can you please explain what this code does.

where Peek(ID) <> ID

order by ID, update_date desc;

Re: update with new time stamp.

userid128223 — Thu, 13 Feb 2014 14:55:13 GMT

I have yet to try your solution. But i will give it a try.

where not exists(ID,ID); // what does second parameter do.

Re: update with new time stamp.

maxgro — Thu, 13 Feb 2014 16:10:33 GMT

Final:

NoConcatenate load *

Resident Source // 1*

where Peek(ID) <> ID // 2*

order by ID, update_date desc; // 3*

1* read from a previous loaded table (resident=already in memory)

2* and 3* :

order by: I want to read the record sorted by ID and, for same ID, update_desc descending

where Peek(ID) <> ID filter: discards rows if the ID is the same of previus read record

example

ID update_date Peek(ID)

1 31-12-2010 keep

1 10-10-2009 1

1 1-1-2007 1

2 1-1-2010 1 keep

2 1-1-2009 2

2 1-1-2008 2

3 12-10-2014 2 keep

4 10-10-2007 3 keep

result: the 4 rows in bold

from online help

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed

Re: update with new time stamp.

userid128223 — Tue, 24 Nov 2020 05:47:18 GMT

Hi Massimo

What if my data has 2 dates and the uniq key is made of 3 fields example:

ID&'|'&Name&'|'CreatedDate as UniqID.

Where CreatedDate is used only to find uniq combination but update date will be used to determine what records gets purged or appended.

How do you handle that scenerio?

Source:LOAD * INLINE ; ConcatenateLOAD * INLINE ; Final:NoConcatenate load*Resident Sourcewhere Peek(ID) <> IDorder by ID, update_date desc; drop table Source;

RESULT

IDNameAmountupdate_date123james500feb-03-2014127John234feb-01-2014432Judy800feb-10-2014547Alex555feb-02-2014

Re: update with new time stamp.

userid128223 — Thu, 13 Feb 2014 20:14:52 GMT

I will mark 2 as correct answer, however I will open up new thread as my requirement is now different then what I posted here.

thanks everyone for help.