topic Dynamic File Name before loading in Script.. in QlikView

Dynamic File Name before loading in Script..

MK_QSL — Wed, 23 Apr 2014 12:10:03 GMT

Hi Experts,

We have a folder where we get file every day. Each file is having date as below.. some of the filenames are shown below.

FileName_09032014

FileName_10032014

….

FileName_08042014

FileName_09042014

FileName_10042014

Now our client’s requirement is that file having date 10^th of every month should be loaded, as the forecast calculation is based on 10^th of every month to 9^th of the next month.

So, on March 10^th the file FileName_10032014 should be loaded and it will be keep loaded until 10^th April. Now on April 10^th FileName_10042014 will be loaded. File having other dates are being used in other apps and should not be loaded at all.

Let me know how can we achieve this in QlikView Script?

I need the dynamic solution and don’t want to update the filename 10^th of every month.

Thanks in advance.

Re: Dynamic File Name before loading in Script..

Wed, 23 Apr 2014 12:18:21 GMT

Hi,

i think, you may use for each clause (+ some condiotions on file name inside it):

FOR Each File in filelist (Root&' \*.' &Ext)
LOAD
'$(File)' as Name,
FileSize( '$(File)' ) as Size,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
NEXT File

Re: Dynamic File Name before loading in Script..

Wed, 23 Apr 2014 12:28:04 GMT

LET vMonthYear = Date(Today()),'MMYYYY');

LET vDirectory = 'D:\Qlikview\10'&$(vMonthYear)&'.xls';

LOAD *

FROM $(vDirectory);

Re: Dynamic File Name before loading in Script..

its_anandrjs — Wed, 23 Apr 2014 12:29:32 GMT

Hi Manish,

You can use for loop for the excel file reading or may be you can use * for loading multiple excel file loading

1. With for loop

let vPath = 'C:\test';

for each vFile in FileList('$(vPath)\*.xlsx')

TableName:

load

'$(vFile)' as SourceFile,

from [$(vFile)]

(ooxml, embedded labels, table is Sheet1);

2. Using * symbol

LOAD *

FROM

(ooxml, embedded labels, table is Sheet1);

Regards

Re: Dynamic File Name before loading in Script..

MK_QSL — Wed, 23 Apr 2014 12:40:30 GMT

Thanks for your reply...

I sorted out by myself as below...

===================================

Let vDayToday = 10;

Let vMonthToday = NUM(IF(Day(Today())>10,Month(Today()),Month(Today())-1),'00');

Let vYearToday = Year(Today());

Directory;

LOAD

FROM

FileName_$(vDayToday)$(vMonthToday)$(vYearToday).txt

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

=============================================