topic Re: Re: how to get last update time while loading in QlikView

how to get last update time while loading

Tue, 07 Oct 2014 11:22:36 GMT

Hi Friends

I am loading a text file from D drive.

Can I get the last update time of the text file on drive.

Thanks

Re: how to get last update time while loading

aveeeeeee7en — Tue, 07 Oct 2014 11:28:23 GMT

Use Text Box Expression:

'Last reload on - ' & reloadtime()

Regards

Av7eN

Re: how to get last update time while loading

jyothish8807 — Tue, 07 Oct 2014 11:29:28 GMT

Hi Pavan

Try this:

='[ Updated at ' & timestamp(reloadtime(), 'hh:mm on MMM DD, YYYY') &' ]'

Regards

Re: how to get last update time while loading

maxgro — Tue, 07 Oct 2014 11:30:51 GMT

filetime

Returns a timestamp for the date and time of the last modification of the file filename. If no filename is specified, the function will refer to the currently read table file.

Examples:

filetime( 'xyz.xls' )

Will return the timestamp of the last modification of the file xyz.xls.

Load *, filetime() as X from abc.txt ;

Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.

Re: how to get last update time while loading

Tue, 07 Oct 2014 11:38:29 GMT

Thanks Friends

Now I have a file with column of last update of text files.

Now I want to reload only those files which are update in this month.

And if possible display message of file names which are not updated.

Thanks

Re: Re: how to get last update time while loading

maxgro — Tue, 07 Oct 2014 12:22:18 GMT

DIRECTORY;

FOR Each File in filelist ('.\*.qvd')

  LOAD
  '$(File)' as FileName,
  FileTime( '$(File)' ) as FileTime,
  if(FileTime( '$(File)' ) >= MonthStart(Today()), 1, 0) as FlagRead
  autogenerate 1;

  let ft=peek('FlagRead');
  let fn=peek('FileName');
  trace ft=$(ft);
  if ($(ft)=1) then
  trace ***** read $(fn);
  ELSE
  trace ***** dont read $(fn);
  ENDIF;

NEXT File

Re: Re: how to get last update time while loading

Wed, 08 Oct 2014 09:09:42 GMT

Hi Grossi,

Please explain this part of script how the logic works

trace ft=$(ft);

if ($(ft)=1) then
trace ***** read $(fn);
ELSE
trace ***** dont read $(fn);
ENDIF;

Re: Re: how to get last update time while loading

maxgro — Wed, 08 Oct 2014 09:49:40 GMT

I used trace just to explain the logic; trace print a message in the logfile

You have to replace the line with trace in bold with a read from the file

// ft variable (FlagRead in table ) is 1 when the time of the file is in today month

if ($(ft)=1) then

// replace this with read from file (load ....)

trace ***** read $(fn);

ELSE

// print an information in the logfile

trace ***** dont read $(fn);

ENDIF;

Re: Re: how to get last update time while loading

Wed, 08 Oct 2014 10:30:49 GMT

Hi Grossei thanks But need more help. I have written the code

DirectoriesToScan:
LOAD * INLINE [
Dirspec
D:\scalability_test\9_30\9_30
]
;
SUB doDir (dir)
FOR EACH file in filelist('$(dir)' & '\*.txt')

Files:
LOAD
'$(file)' as FilePath,
FileSize('$(file)') as FileSize,
FileTime('$(file)') as FileTime,
if(FileTime( '$(file)' ) >= MonthStart(Today()), 1, 0) as FlagRead,
subfield('$(file)', '.', -1) as FileExtension,
subfield('$(file)', '\', -1) as FileName,
subfield (subfield('$(file)', '\', -1),'.' ,1) as Naam
AUTOGENERATE 1
;
NEXT
FOR EACH subdir in dirlist( '$(dir)' & '\*' )
CALL doDir('$(subdir)')
NEXT
END SUB

SUB doRoot (root)
FOR EACH subdir in dirlist( '$(root)' )
CALL doDir('$(subdir)')
NEXT
END SUB

FOR i = 0 to FieldValueCount('Dirspec')
Call doRoot(FieldValue('Dirspec',$(i)));
NEXT i
let ft=peek('FlagRead');
let fn=peek('FileName');
if ($(ft)=1) then
OGIS:
Load * from ;
Next File

The problem is again its loading only one file from Germany folder.

I have two files in Germany and another two in Mexico folder.

I want to load all the the files at a go. With the current code I am able to get only one file into qlikview.

Thanks for your time