topic Re: Incremental Loading Error. in QlikView

Incremental Loading Error.

s10157754 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Qlikview Experts,

I was using my previous qlikview script to implement same incremental loading in another Qlikview Dashboard which importing another type of excel files (.csv)

I have no idea where went wrong in my following script, I just can't import the files into my Qlikview Dashboard, perhaps anyone of you guys can help me tackle on this problem? The screenshot pasted below was the error that I encountered.

Below was my script and I had attached my sample file for your reference. Appreciated so much for your help!

Let vDate = Date(Date#('24-Mar-2018','DD-MMM-YYYY'),'DD-MMM-YYYY');

SUB Scanfolder(Root)

let ControlQVD = left(Root,Index(Root,'\',-1))&'control.qvd';
Let SummaryQVD = left(Root,Index(Root,'\',-1))&'SummaryTable.qvd';

If FileSize(ControlQVD) then
control:load filename, timestamp from [$(ControlQVD)](qvd);
else
control:load '' as filename, now() as timestamp AutoGenerate 0; // EmptyTable
Endif

For each FileExtension in 'csv'
For each FoundFile in FileList(Root &'\????????-bc-job-cycle-time.'& FileExtension)

Let vFileDate = Date(Date#(Left(FileName(),8),'YYYYMMDD'),'DD-MMM-YYYY');

If vFileDate >= vDate Then

Let t = alt(FieldIndex('filename','$(FoundFile)'),0);

If t = 0 then
control: load '$(FoundFile)' as filename, now() as timestamp AutoGenerate 1; // Keep record of the newly added file;

SummaryTable:
LOAD
@1 as Date,
@2 as Machine,
@3 as Result,

Date(Date#(Left(FileName(),8),'YYYYMMDD'),'DD-MMM-YYYY') as REALDATE

FROM [$(FoundFile)]
(txt, codepage is 1252, no labels, delimiter is ',', msq);

Endif
Endif
Next FoundFile
Next FileExtension

If NoOfRows('control') > 0 then // Check if the control table has been incremented?
store control into [$(ControlQVD)](qvd);
Drop Table control;
Endif

If NoOfRows('SummaryTable') > 0 then // Check if the summary table has been incremented?
If FileSize('$(SummaryQVD)') > 0 then
Concatenate(SummaryTable) load * from [$(SummaryQVD)](qvd);
Endif
store SummaryTable into [$(SummaryQVD)](qvd);
Drop Table SummaryTable;
Endif

End Sub

Call ScanFolder('C:\Users\qianning\Desktop\AutoMI\AutoMI2'); //Loop through all the crane folders

IncrementalLoad: //Displayed the newly added file and concatenate with the previous data model
LOAD Date,
Machine,
Result

FROM

(qvd);

Re: Incremental Loading Error.

Chanty4u — Thu, 29 Mar 2018 05:44:25 GMT

the error saying file not found ,

SummaryTable is not present in the location? or it is with someother name?

Re: Incremental Loading Error.

PrashantSangle — Thu, 29 Mar 2018 06:04:20 GMT

Hi,

check below case

1: as chanty suggested summaryTable.qvd is present at that location.

2: Check this condition

FoundFile in FileList(Root &'\????????-bc-job-cycle-time.'& FileExtension)

in your script.

$FoundFile variable executing properly??

Regards,

Re: Incremental Loading Error.

s10157754 — Thu, 29 Mar 2018 06:44:46 GMT

Dear chanty,

The original loading script in another Qlikview Dashboard was pasted below and I had also attached a sample of loading files for your reference. Basically after I shift the script over to my new dashboard, I had only changed 2 lines which highlighted in RED, end up my new script unable to function anymore. However, I can't figure out the reason behind why it cannot work

(The reason why I modified the 2lines highlighted in RED is because I have no idea what does it mean)

Let vDate = Date(Date#('23-Mar-2018','DD-MMM-YYYY'),'DD-MMM-YYYY');

SUB Scanfolder(Root)

let ControlQVD = left(Root,Index(Root,'\',-1))&'control.qvd';
Let SummaryQVD = left(Root,Index(Root,'\',-1))&'SummaryTable.qvd';

If FileSize(ControlQVD) then
control:load filename, timestamp from [$(ControlQVD)](qvd);
else
control:load '' as filename, now() as timestamp AutoGenerate 0; // EmptyTable
Endif

For each FileExtension in 'txt'
For each FoundFile in FileList(Root &'\BCAS_???_????_???_????.'& FileExtension)

Let vFileName = Right(Replace(SubField('$(FoundFile)','\',-1),'.txt',''),13);
Let vFileDate =Date(Date#(Left('$(vFileName)',2) & Right('$(vFileName)',9) ,'DD_MMM_YYYY'),'DD-MMM-YYYY');

If vFileDate >= vDate Then

Let t = alt(FieldIndex('filename','$(FoundFile)'),0);

If t = 0 then
control: load '$(FoundFile)' as filename, now() as timestamp AutoGenerate 1; // Keep record of the newly added file;

SummaryTable:
LOAD
@1 as Date,
@2 as Time,
@3 as Pm

FROM [$(FoundFile)]
(txt, codepage is 1252, no labels, delimiter is ';', msq);
Endif
Endif
Next FoundFile
Next FileExtension

If NoOfRows('control') > 0 then // Check if the control table has been incremented?
store control into [$(ControlQVD)](qvd);
Drop Table control;
Endif

If NoOfRows('SummaryTable') > 0 then // Check if the summary table has been incremented?
If FileSize('$(SummaryQVD)') > 0 then
Concatenate(SummaryTable) load * from [$(SummaryQVD)](qvd);
Endif
store SummaryTable into [$(SummaryQVD)](qvd);
Drop Table SummaryTable;
Endif

End Sub

Call ScanFolder('C:\Users\qianning\Desktop\AutoMI\AutoMI2'); //Loop through all the machinefolders

IncrementalLoad: //Displayed the newly added file and concatenate with the previous data model
LOAD Date,
Time,
Pm

FROM

(qvd);

Re: Incremental Loading Error.

s10157754 — Thu, 29 Mar 2018 06:46:02 GMT

Dear Sangle,

(The reason why I modified the 2lines highlighted in RED is because I have no idea what does it mean)

Call ScanFolder('C:\Users\qianning\Desktop\AutoMI\AutoMI2'); //Loop through all the machinefolders

IncrementalLoad: //Displayed the newly added file and concatenate with the previous data model
LOAD Date,
Time,
Pm

FROM

(qvd);

Re: Incremental Loading Error.

PrashantSangle — Thu, 29 Mar 2018 07:01:12 GMT

Hi,

I can see there are more than 2 changes

posted in script

For each FileExtension in 'csv'
For each FoundFile in FileList(Root &'\????????-bc-job-cycle-time.'& FileExtension)

Let vFileDate = Date(Date#(Left(FileName(),8),'YYYYMMDD'),'DD-MMM-YYYY');

Posted in above comment

For each FileExtension in 'txt'
For each FoundFile in FileList(Root &'\BCAS_???_????_???_????.'& FileExtension)

Let vFileName = Right(Replace(SubField('$(FoundFile)','\',-1),'.txt',''),13);
Let vFileDate =Date(Date#(Left('$(vFileName)',2) & Right('$(vFileName)',9) ,'DD_MMM_YYYY'),'DD-MMM-YYYY');

Can you answer below query.

1: do you have below folder location in machine

C:\Users\qianning\Desktop\AutoMI\AutoMI2

2: Can you give us some sample filename with extension which you want to load ??

Regards,

Re: Incremental Loading Error.

s10157754 — Thu, 29 Mar 2018 07:48:50 GMT

Dear Sangle,

Since I was loading two different type of files from 2 different location, therefore the minor changes such as file extension and file location I have changed according and I am certain that it works. Only for the part that I highlighted in RED, I changed it to another coding, resulting the new script was unable to work anymore. I had attached the sample of loading files for your reference at top, but I will attach again to ease your reference.

The sample of loading files in txt format works perfectly with original loading script.

The sample of loading files in csv format unable to work with my new script after I had changed that 2 lines highlighted in RED.

Best Regards

QianNing