Performance enhancement in script

[vikasmahajan]

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 '$(*)');

NEXT

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

[kaushiknsolanki]

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

[vikasmahajan]

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

[vikasmahajan]

Thanks Kaushik

[kaushiknsolanki]

Hi,

Please correct the answer the close the post.

Regards,

Kaushik Solanki