topic Re: Incremental load error - loading QVD failing in QlikView

Incremental load error - loading QVD failing

Anonymous — Sat, 20 Dec 2014 08:09:42 GMT

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

Re: Incremental load error - loading QVD failing

vivek_niti — Sat, 20 Dec 2014 08:19:06 GMT

Hi,

your variable vFolderSourceData is not returning any value.

try this:

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

Re: Incremental load error - loading QVD failing

Anonymous — Sat, 20 Dec 2014 09:50:01 GMT

HI Vivek

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

Many thanks

Chris

Re: Incremental load error - loading QVD failing

terezagr — Sat, 20 Dec 2014 10:41:16 GMT

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.

Re: Incremental load error - loading QVD failing

MK_QSL — Sat, 20 Dec 2014 10:42:43 GMT

?????????

Is below one OK?

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

Re: Incremental load error - loading QVD failing

Anonymous — Sat, 20 Dec 2014 10:59:37 GMT

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

Re: Incremental load error - loading QVD failing

terezagr — Sat, 20 Dec 2014 11:16:04 GMT

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

Re: Incremental load error - loading QVD failing

Anonymous — Sat, 20 Dec 2014 11:34:44 GMT

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

Regards

Chris

Re: Incremental load error - loading QVD failing

terezagr — Sat, 20 Dec 2014 11:41:29 GMT

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

Re: Incremental load error - loading QVD failing

Anonymous — Sat, 20 Dec 2014 12:16:15 GMT

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.