Performance enhancement in script

vikasmahajan — Wed, 02 Mar 2016 06:57:03 GMT

Hi,

Please find my script for checking old excel file names and new excel files using timestamps.

The script // To get name of new excel files in folder using where not exists(OldFileName,FileName()) ; is taking too much time for execution for checking 4 excel files it is taking around 17 Mins. How to improve this script . I want to compare all directory excels files with whether user has copied or modified existing excels files using timestamps and filename function,

Thanks

Vikas

Script :

////

//// 3 Tab - Code to check if any New file is arrived in folder.

//// If new file is arrived it will load Only New file and

//// will Overwrite the existing QVD with New file data.

//// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

// First need to check if any New files are placed in folder

Temp:

LOAD Distinct

FileName as OldFileName

FROM

[$(vQVDFilePath)ALLQVD.QVD]

(qvd);

// To get name of new excel files in folder

NewFile:

First 1 LOAD FileName() as NewFile

FROM

[$(vExcelFilePath)Asset*.xlsx]

(ooxml, embedded labels, table is '$(*)')

where not Exists(OldFileName,FileName());

DROP Table Temp;

NewExcelFile:

LOAD * Inline [

Temp ];

if NoOfRows('NewFile')<=0 THEN

TRACE "New file not found";

DROP Table NewExcelFile;

ELSE

for i=1 to FieldValueCount('NewFile')

LET vNewFile = FieldValue('NewFile',$(i));

Concatenate(NewExcelFile)

LOAD *,

FileName() as FileName,

timestamp(FileTime(),'DD/MM/YYYY hh:mm:ss') as FileTime,

Date(date#(charge_month,'YYYYMM'),'DD-MM-YYYY') as PostingDate

FROM

[$(vExcelFilePath)$(vNewFile)]

(ooxml, embedded labels, table is '$(*)');

//To load the data which is updated

Final:

NoConcatenate

LOAD *

Resident NewExcelFile;

DROP Table NewExcelFile;

// to load the Data from existing QVD

Concatenate(Final)

LOAD *

FROM

[$(vQVDFilePath)ALLQVD.qvd]

(qvd);

DROP Field Temp From Final;

Temp2:

Load

Distinct FileName,Timestamp(FileTime) as FileTime,count(1)

resident Final Group by FileName,FileTime ;

STORE Final into $(vQVDFilePath)ALLQVD.qvd;

Store Temp2 into $(vQVDFilePath)FileDetails.QVD;

DROP Table Final;

DROP Table Temp2;

ENDIF

DROP Tables NewFile;

LET i= Null();

LET vNewFile =Null();

Re: Performance enhancement in script

kaushiknsolanki — Wed, 02 Mar 2016 07:05:25 GMT

Hi Vikas,

Instead of this way of finding the new file name why don't you use file functions to get the list of files and then find which one is new and old.

Refer to For Each Next script in help, for sample script.

Regards,

Kaushik Solanki

Re: Performance enhancement in script

vikasmahajan — Wed, 02 Mar 2016 07:32:22 GMT

I got solution , Modified script as follows

Temp:

LOAD Distinct

FileName as OldFileName

FROM

[$(vQVDFilePath)ALLQVD.QVD]

(qvd);

// To get name of new excel files in folder

FOR Each File in filelist('$(vExcelFilePath)AssetTracking*.xlsx')

NewFile:

LOAD

//'$(File)' as FileName,

subfield('$(File)','\','-1') as FileName1,

FileName()

autogenerate 1

where not Exists(OldFileName,subfield('$(File)','\','-1'));

NEXT File;

Thanks

Vikas

Re: Performance enhancement in script

vikasmahajan — Wed, 02 Mar 2016 07:45:05 GMT

Thanks Kaushik

Re: Performance enhancement in script

kaushiknsolanki — Wed, 02 Mar 2016 15:25:03 GMT

Hi,

Please correct the answer the close the post.

Regards,

Kaushik Solanki

topic Re: Performance enhancement in script in QlikView

Performance enhancement in script

Re: Performance enhancement in script

Re: Performance enhancement in script

Re: Performance enhancement in script

Re: Performance enhancement in script