topic Re: Load only the two latest files in QlikView

Load only the two latest files

KaisGarci — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I have files ( All of them are in the same format) named as for example 'REAL_1008' and the '1008' is the date as the 10th of August. I want only load the two latest fles based on that date.

Can anyone help me please ?

Thank you

Re: Load only the two latest files

martinpohl — Mon, 17 Aug 2020 11:24:52 GMT

Hello

take this:

for each vFile in filelist('YourPath\REAL_*')

Files:

load

'$(vFile)' as File,

filetime() as Timestamp

autogenerate (1);

Files_Sorted:

noconcatenate load * resident Files order by Timestamp DESC;

drop table Files;

for vLoadfiles = 0 to 1

let vLoadfile = peek('File'),$(vLoadfiles),'Files_Sorted)';

load *

from, '$(vLoadfile )'

;

drop table Files_Sorted;

I would take filetime instead of numbers in filename because there is no year in.

Regards

Re: Load only the two latest files

KaisGarci — Mon, 17 Aug 2020 11:51:00 GMT

Thank you for helping me.

But it didn't work.

Re: Load only the two latest files

martinpohl — Mon, 17 Aug 2020 11:55:26 GMT

there is a script error

change

let vLoadfile = peek('File'),$(vLoadfiles),'Files_Sorted)';

let vLoadfile = peek('File'),$(vLoadfiles),'Files_Sorted';

Regards

Re: Load only the two latest files

KaisGarci — Fri, 21 Aug 2020 11:27:25 GMT

Hello @martinpohl

Thanks for helping but it still give an error.

Re: Load only the two latest files

martinpohl — Fri, 21 Aug 2020 11:33:53 GMT

let vLoadfile = peek('File',$(vLoadfiles),'Files_Sorted');

Re: Load only the two latest files

KaisGarci — Fri, 21 Aug 2020 11:54:39 GMT

It load but it gives me an empty table

Re: Load only the two latest files

Kushal_Chawda — Mon, 24 Aug 2020 13:36:38 GMT

try below. Change the path as per your folder location

for Each vFile in FileList('C:\REAL_*.xlsx')

Files:
LOAD subfield('$(vFile)','\',-1) as FileName,
date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month,
'$(vFile)' as Path
AutoGenerate 1;

Files_needed:
NoConcatenate
first 2 LOAD *
Resident Files
Order by Month desc;

DROP Table Files;

Data:
LOAD * Inline [
Temp ];

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

DROP Table Files_needed;

DROP Field Temp;

Re: Load only the two latest files

KaisGarci — Mon, 24 Aug 2020 11:32:06 GMT

Thank you @Kushal_Chawda . this worked perfectly, but I want to add a variable called date which gives me the date of the 2 latest file.

Thank you

Re: Load only the two latest files

Kushal_Chawda — Mon, 24 Aug 2020 11:47:25 GMT

you can add one variable as highlighted below in part of the code

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

let vMonth = FieldValue('Month',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName,

'$(vMonth)' as MONTH
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

Re: Load only the two latest files

KaisGarci — Mon, 24 Aug 2020 13:00:02 GMT

Thank you @Kushal_Chawda. This was really helpfull. 😄