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