Building a QVD for records that have changed since the last load
Hi All
I am trying to build a script that will find the latest date from the QVD and then pull that data from SQL and concatenate with the existing data. However I cannot get the date syntax to work correctly when trying to filter the QVD. I have eliminated the SQL portion and am now just trying to get the lastest date and then laod all of the records with a date earleir than that date. See below
Table1:
Load Distinct
date(Max([UsageHour])) as MaxUsageHour
FROM BaseLine.qvd (qvd);
Let vMaxUsageHour=date(Peek('MaxUsageHour'));
Load [BaseLineUsage],[ResidenceId],[UsageDateHour], BaseLineUsageDate, [DateTimeStamp] , date(UsageHour) as UsageHour
FROM BaseLine.qvd (qvd)
//where date(UsageHour) < $(vMaxUsageHour);
where date(UsageHour) < Date($(vMaxUsageHour),'MM/DD/YYYY')
When I do this the load returns zero records. When I reverse it and say load all of the records from the QVD that have dates late than the max date in the QVD (which by definition should return nothing) it actualls returns all of the date from earlier years and the current year. It does not seem to be an alpha match as it returns 1/1/2012 as being > 4/11/12 as well.
Any help would be appreciated. I am assuming it is something to do with the format of the date but I cannot hit the right syntax for the life of me.
Jonathan is right on. Without the quotes, your vMaxUsageHour is being treated as the expression 4 / 11 / 12 which yields some number smaller than 1.
It is easier to filter the loading of existing QVD rows by using WHERE NOT EXISTS(key), assuming you have already loaded the update rows -- that contain "key" from SQL.
Note that if your data does not have a primary key, you can still use Qvc by setting the variable:
SET Qvc.Loader.v.KeyFieldIsUnique=0;
You do seem to have a logical primary key (ResidenceId & UsageHour) but there is no need to make a key unless older rows get updated.
Based on your logic above, it looks like you just need to replace all rows in the QVD with delta rows that have the same or new UsageHour. So the Qvc code looks like:
Thanks for the response. I did actually figure it out in the end. The problem was putting the DATE function in the initial value check
Load Distinct
date(Max()) as MaxUsageHour
FROM BaseLine.qvd (qvd);
I changed to
Load Distinct
(Max() as MaxUsageHour
FROM BaseLine.qvd (qvd);
Once I did this the queries worked correctly and interpreted the dates correctly. I did not put the quotes around the vMaxUsageHour and it still seems to work correctly. I will try this also to see if it make a difference but the script does correctly process the incrementals at this point,
I will also look at the NOT EXISTS, although I am curious why this would be more efficient than a < condition. In this case we are looking at meter reads that are all incremental so there is never an update only inserts, so it would seem that the approach of finding latest date and loading that from the QVD and the later records from SQL would be OK. In this case I am actually truncating the timestamp portion so I am actually reloading the day transactions each time rather than say just things in the last 15 minutes which may be excessive, but it tends to avoid the possibility of things falling through the cracks with time discrepancies.
Re NOT EXISTS vs < $(UsageHour). It's primarily a style difference. Both would provide correct results. The difference may be in performance. WHERE NOT EXISTS may qualify as an optimized load, which would would be faster. < will definitely not be an optimized QVD load, which will take longer. The difference in your case may not be worth worrying about.
