topic Re: Load file whose name has a date that changes everyday in QlikView

Load file whose name has a date that changes everyday

Tue, 22 Sep 2015 15:04:25 GMT

Hello everyone,

Every night, an updated file is uploaded. It has a name like file_20150922.csv (file_YYYYMMDD.csv) with the date of yesterday.

I want my application to point on the last version of the file eg. today, my file will point on file_20150921.csv but tomorrow, it will have to point on file_20150922.csv.

I use something like

Load

...

FROM file_20150922.csv

Is it possible ?

Can I do something like:

From file_Today()-1 ??

Thank you for your help

Re: Load file whose name has a date that changes everyday

Tue, 22 Sep 2015 15:08:35 GMT

Yes you can use something like:

set vDataLoad = Date(Today(),'YYYYMMDD');

Load *

From file_$(vDataPath).csv;

Re: Load file whose name has a date that changes everyday

alexandros17 — Tue, 22 Sep 2015 15:09:20 GMT

Use a variable:

LET vDate = WeekYear(Today()) & Num(Month(Today()),'00') & Num(Day(Today()),'00');

then

Load .... Resident File_$(vDate).csv;

Let me know

Re: Load file whose name has a date that changes everyday

MK_QSL — Tue, 22 Sep 2015 15:12:30 GMT

FileName:
LOAD 
  Date#(TextBetween(FileName(),'_','.'),'YYYYMMDD') as FileDate
FROM
*.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


Date:
Load Max(FileDate) as MaxFileDate Resident FileName;


Drop Table FileName;


Let vMaxDate = Date(Peek('MaxFileDate',0,'Date'),'YYYYMMDD');


Drop Table Date;


FinalTable:
LOAD 
  Date, 
    Sales
FROM
file_$(vMaxDate).csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

The above script will first find the file having maximum date as name and load it..

Re: Load file whose name has a date that changes everyday

Anonymous — Tue, 22 Sep 2015 17:08:17 GMT

you should use in your script

LET vDate= date(today()-1,'YYYYMMDD');

Load

...

FROM file_$(vDate).csv

Regards

Re: Load file whose name has a date that changes everyday

Tue, 22 Sep 2015 17:15:41 GMT

I am currently doing something exactly like that, so as long as each day, the file layout is exactly the same, and only the data chnges, and the files are in the same directory, you can use a wild card in your file name

Load

FROM
[..\AB_*_Report.xlsx]
(ooxml, embedded labels, table is Flash_Report);

based on my naming convention the data bewteen the '_', which is where the wild card is, is a date. As long as the file is put in the directory with that naming convention the reload picks it up