Incremental load error - loading QVD failing

[Anonymous]

I am doing an incremental load through the following script:

//Load Incremental Data only from the main excel table after having created a QVD on initial load
LET vLoadTime = Num(Now());
[Main Data]:
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,
     [Repeat or Original],
//   [Doctor Name],
//     [Doctor Number],
     QTY,
      [MedAid Amount],
      Shortfall,
      [Med Aid] as [Payment Type],
      [Supply days],
      //[Drug Code]
     Num([Drug Code],'000000') as [Drug Code], 
      Num([Doctor Number],'00000') as [Doctor Number],
      [Retail Pharmacy],
      Branch,
    num(Date_Modified) as Date_Modified
FROM
[$(vFolderSourceData)Datawarehouse_2.xlsx]
(ooxml, embedded labels, table is Datawarehouse)
Where Date_Modified >= $(vLoadTime) and Date_Modified <= $(vLoadTime);

//Initial store [Main Data] into QVD commented out after initial load
//STORE [Main Data] into '$(vFolderSourceData)QVDs\
//PrescriptionHistory.qvd' (qvd);

//Concatenate with QVD
Concatenate ([Main Data])
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,
     [Repeat or Original],
//   [Doctor Name],
//     [Doctor Number],
     QTY,
      [MedAid Amount],
      Shortfall,
      [Med Aid] as [Payment Type],
      [Supply days],
      //[Drug Code]
     Num([Drug Code],'000000') as [Drug Code], 
      Num([Doctor Number],'00000') as [Doctor Number],
      [Retail Pharmacy],
      Branch,
      num(Date_Modified) as Date_Modified
FROM '$(vFolderSourceData)QVDs\
PrescriptionHistory.qvd' (qvd)
Where not Exists([Script Number]);

//Replace Old QVD file
IF ScriptErrorCount = 0 then
STORE [Main Data] into '$(vFolderSourceData)QVDs\
PrescriptionHistory.qvd' (qvd);
//LET vLastLoadTime = vLoadTime;
END If

The initial incremental load for fresh data goes fine according to the following screen shot (as the data has not been updated with any fresh data so we expect zero records loaded):

However, i get an error when try to fetch the QVD file.  The error is as per the following screen shot:

The same folder which the system says it cannot find is the same folder from which the Excel file is being loaded from.What could be the problem?

Your assistance will be greatly appreciated.

Regards.

Chris

[vivek_niti]

Hi,

your variable vFolderSourceData is not returning any value.

try this:

$(vFolderSourceData)QVDs\PrescriptionHistory.qvd (qvd)

[Anonymous]

HI Vivek

Many thanks for your prompt response, most appreciated.  i tried your recommendation and the following same errors comes out:

Many thanks

Chris

[terezagr]

I think your assumption that the first part of the load is fine is wrong. You are basically saying load everything that is >=now and in the same time everything <=now. Therefore, you are loading 0 data.

Also, I think your relative path is set incorrectly. How does your $(vFolderSourceData) looks like? It seems like you are not reaching the right directory...

Try to use instead of the variable absolute path for the qvd.

[MK_QSL]

?????????

Is below one OK?

Where Date_Modified >= $(vLoadTime) and Date_Modified <=$(vLoadTime);

[Anonymous]

Many thanks for your input.  I removed the <=  and left the >= in the where clause.

I used the absolute path for loading the QVD by locating the file and the following error pops up:

Regards

Chris

[terezagr]

LET vLoadTime = Num(Now());
[Main Data]:
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,

     [Repeat or Original],
//   [Doctor Name],

//     [Doctor Number],

     QTY,
      [MedAid Amount],
      Shortfall,
      [Med Aid] as [Payment Type],
      [Supply days],
      //[Drug Code]

     Num([Drug Code],'000000') as [Drug Code], 
      Num([Doctor Number],'00000') as [Doctor Number],
      [Retail Pharmacy],
      Branch,
    num(Date_Modified) as Date_Modified
FROM
[..\Datawarehouse_2.xlsx](ooxml, embedded labels, table is Datawarehouse);

Concatenate ([Main Data])
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,

     [Repeat or Original],
//   [Doctor Name],

//     [Doctor Number],

     QTY,
      [MedAid Amount],
      Shortfall,
      [Med Aid] as [Payment Type],
      [Supply days],
      //[Drug Code]

     Num([Drug Code],'000000') as [Drug Code], 
      Num([Doctor Number],'00000') as [Doctor Number],
      [Retail Pharmacy],
      Branch,
      num(Date_Modified) as Date_Modified
FROM ..\QVDs\PrescriptionHistory.qvd (qvd)
Where not Exists([Script Number]);

STORE [Main Data] into ..\QVDs\PrescriptionHistory.qvd (qvd);

[Anonymous]

Hi

Many thanks, the following script error is raised when using the suggested script:

Regards

Chris

[terezagr]

Ok, try to look for the file…Does the file really exists in the location specified? Where is your file exactly saved?

[Anonymous]

Many thanks for all your efforts.  Yes the file exists and is in a QVD folder where the  Datawarehouse_2 excel file is.  I think this could be an issue with running this on a Parallels Desktop virtual machine on a MacBook.  Would it be.  I have actually located the qvd file in the QVD folder.

Regards.