Incremental Load problems

Fri, 03 Jun 2011 16:10:32 GMT

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])
;

Incremental Load problems

prieper — Mon, 06 Jun 2011 19:43:02 GMT

Think that you need to qualify, to which table you want to concatenate the second table? Either give a new name, otherwise most likely the first table is called operasql.

Thus the qvd should be loaded with CONCATENATE (operasql) LOAD .....

HTH
Peter

topic Incremental Load problems in QlikView

Incremental Load problems

Incremental Load problems