Concatenate & Update

[Anonymous]

Hello all,

I have a script that does not work and I am not sure why.  I have an exisiting QVD file with a date and related data on every row.  Every day I update this existing QVD with one new day of data plus updating two prior weeks.  I tried two solutions with the same result. It only kept the New/Updated data and wiped the existing. Any advice as to what is going on? Thanks in advance.

What I did :

1) Load the "New Data" first which is comprised of the prior two weeks and the prior day (Excel file)

2) Load (Concatenate) the existing QVD onto the new data where the date doesn't already exist.

OR

2)  create variable holding minimum date of new/update data

3) Load (Concatenate) the existing QVD onto the new data where date is less than the new data

SCRIPT

New Data:

Load

Date,

field1,

field2,

FROM

C:\ New&Updated.xlsx

concatenate LOAD * from C:\Existing.QVD Where not Exists Date;

OR

Let vNewMinDate = min(Date)

concatenate LOAD * from C:\Existing.QVD Where Date<'$(vNewMinDate)';

[swuehl]

I think both approaches should work in principle.

But in solution 1), I think you need to write

concatenate LOAD * from C:\Existing.QVD Where not Exists(Date);

and assuming that your Date field shows not your historic dates at that point of time (e.g. that you haven't loaded a kind of master calendar already, or another table with all the dates)

In solution 2) you need to find the Min(Date) using a LOAD statement, then querying the min value using a PEEK() function in your variable

MIN:

LOAD Min(Date) as MinDate

RESIDENT [New Data];

Let vNewMinDate = Peek('MinDate',0,'MIN');

[Anonymous]

Thanks Swuehl.  I did have a date with parenthesis around it but failed to write it above.  I do have a master calendar but the field name for the 'Date' is different so there is no ambiguity with use of the field names. Since both should work I will try to use the one without the variable because it seems to be a cleaner method.  I'll keep on trying based on what you said.