topic Re: How to Auto Load Excel Sheet by time specific. in QlikView

How to Auto Load Excel Sheet by time specific.

Thu, 05 Feb 2015 04:34:53 GMT

Hi QV,

I've a folder that contains excel sheet which keep coming every night. I have to use only the Excel Sheet automatically by 02.15 PM.

Here is my source folder.

Can you help me write in the script to just pull files from a certain time period maybe?

Can anyone help me on this please?

Re: How to Auto Load Excel Sheet by time specific.

tresB — Thu, 05 Feb 2015 04:55:05 GMT

Use filetime() to get modification time. Try like:

Load

From yourpath\*.xls where filetime()>yourtime;

Re: How to Auto Load Excel Sheet by time specific.

Thu, 05 Feb 2015 05:00:42 GMT

Hi Tresesco,

this is my source path. Help me out.

**************************************

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

***********************************************

Re: How to Auto Load Excel Sheet by time specific.

tresB — Thu, 05 Feb 2015 05:06:36 GMT

Try like:

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) where hour(filetime())>2;

Re: How to Auto Load Excel Sheet by time specific.

Thu, 05 Feb 2015 05:20:36 GMT

Hi,

It is reading all the excel sheet which ever earlier than 02 PM. I just want to load the only excel sheet with in that time frame. Only one excel sheet. Could you help me on this please?

Ex: 1.45PM to 2.15 PM

Re: How to Auto Load Excel Sheet by time specific.

tresB — Thu, 05 Feb 2015 05:26:48 GMT

where frac(filetime())>time#('1:45', 'h:mm') and frac(filetime())<time#('2:15', 'h:mm')

Modified.

Re: How to Auto Load Excel Sheet by time specific.

manojkulkarni — Thu, 05 Feb 2015 05:35:08 GMT

Use something like

FOR each File in filelist('SourcePath')

LOAD

FileBaseName() As fname,

FileTime() AS filtime

FROM [$(File)] ;

NEXT File

Let vMaxDate = Peek('filtime', -1, 'T');

Re: How to Auto Load Excel Sheet by time specific.

Thu, 05 Feb 2015 05:42:05 GMT

Hi,

It is not loading any records. Just 0 records loaded showing.

Re: How to Auto Load Excel Sheet by time specific.

tresB — Thu, 05 Feb 2015 05:50:19 GMT

Hope you understood the logic. There might be some basic glitch in your code segment, try to find that with trial and error.

Re: How to Auto Load Excel Sheet by time specific.

Thu, 05 Feb 2015 05:53:34 GMT

Hi Manoj,

how to try your login? Help me out.

**************************************

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

***********************************************