Hi All,
I am encountering difficulties with an incremental load script. Perhaps someone has encountered this issue before...
Each day a new excel file is published to a folder location. The excel file contains new and/or updated records with a key identifier.
Each day, I wish to read this excel file, then read records from a QVD file which was created the day before and concatenate the two together, replacing any of the records from the QVD which have been updated in the excel file.
The problem I am having is with the WHERE clause in the concatenation.
Some of the data which I concatenate from the QVD file is superceding the data from the excel file and hence records which should be updated in the newly combined table are still the old data from the QVD ...
Any help much appreciated!
My script so far is:
Let vOORFilename = '..\OOO Rooms_01May11.xls';
LOAD 1 as [Offline Room Count],
RESORT as [Property Number],
num(ROOM) as [Room Number],
num(SEQ) as [Offline ID],
REASON_CODE as [Reason Code],
Date(BEGIN_DATE) as [Offline Date],
IF(isnull(COMPLETED_DATE),Date(END_DATE+1),Date(COMPLETED_DATE+1+1)) as [Returned Online Date],
IF(isnull(COMPLETED_DATE),'Y','N') as [Default Online Ind],
Date(END_DATE) as [Offline End Date],
IF(isnull(COMPLETED_DATE),Date('01/01/9999'),DATE([COMPLETED_DATE]+1)) as [Completed Date],
IF(isnull(COMPLETED_DATE),Date(END_DATE),DATE([COMPLETED_DATE]+1)) as [ReportingCompletedDate],
REPAIR_REMARKS as [Repair Remarks],
LEFT(REPAIR_REMARKS,7) as [Job Log Number]
FROM
$(vOORFilename)
(biff, embedded labels, table is operasql$);
LEFT JOIN
LOAD [Reason Code],
Reason,
[Reason Sub-Group],
[Reason Group]
FROM
[$(XLSPath)Offline Reason Codes - Opera.xls]
(biff, embedded labels, table is Sheet1$);
LEFT JOIN
Load [Property Number],
[Cost Centre],
[Hotel Name]
FROM
$(QVDPath)Hotels.qvd
(qvd) ;
CONCATENATE
LOAD [Offline ID],
[Property Number] as [Alternate],
[Reason Code],
[Offline Room Count],
[Room Number],
[Offline Date],
[Returned Online Date],
[Default Online Ind],
[Offline End Date],
[Completed Date],
ReportingCompletedDate,
[Repair Remarks],
[Job Log Number],
Reason,
[Reason Sub-Group],
[Reason Group],
[Cost Centre],
[Hotel Name]
FROM
[$(QVDPath)Opera Offline Rooms.qvd] (qvd)
WHERE
NOT EXISTS([Offline ID])
;
