Latest 3 Records

[maniram23]

Hi

i have folder path is like   d:\qlikview\source

in this i have the files like

sales_01_01_2016.xlsx

sales_01_02_2016.xlsx

sales_01_03_2016.xlsx

sales_01_04_2016.xlsx

sales_01_06_2016.xlsx

sales_01_09_2016.xlsx

sales_01_10_2016.xlsx

i have excel like this from this folder i want load only top 3 excels it means latest TOP 3 DATES excel

in above example i want load only  01_10_2016,01_09_2016,01_06_2016  xl files only

how to write automation code for this

tried lot but no idea?

[avinashelite]

check this thread you will get the idea

how to Load only the latest files?

[tamilarasu]

Hi,

Try,

Files:
LOAD 
Date(Date#(Right(FileBaseName(),10),'DD_MM_YYYY'))  as FileDate,
FilePath()                                          as FileName
FROM [Path\*.xlsx];

NoConcatenate
Latest:
First 3 LOAD
FileDate,
FileName
Resident Files order by FileDate Desc; 

DROP Table Files;

LET NumRows = NoOfRows('Latest');

FOR i=1 to $(NumRows)
LET vFileName =FieldValue('FileName',$(i));
LOAD *
From $(vFileName) (ooxml, embedded labels, table is Sheet1);
NEXT

[MK_QSL]

Let vPath = 'D:\qlikview\source';


TempDate:
LOAD 
  Date(Date#(SubField(Mid(FileName(),7),'.',1),'DD_MM_YYYY')) as FileDate
FROM

(ooxml, embedded labels, table is Sheet1);


TempDate2:
Load 
  RowNo() as ID, 
  Date(FileDate,'DD_MM_YYYY') as FileDate 
Resident TempDate 
Where RowNo() < 3
Order By FileDate Desc;


Drop Table TempDate;


Let vMinDate = Num(Peek('FileDate',-1,'TempDate2'));


Final:
LOAD
  *, 
  Date(Date#(SubField(Mid(FileName(),7),'.',1),'DD_MM_YYYY')) as FileDate
FROM

(ooxml, embedded labels, table is Sheet1)
Where Date#(SubField(Mid(FileName(),7),'.',1),'DD_MM_YYYY') >= '$(vMinDate)';


Drop Table TempDate2;

[ajsjoshua]

Hi,

check thisQlikView - Loading Data From Multiple Files - YouTube

[Anonymous]

Hi Mani,

You could try to use a for each statement to load all the files in your current directory that meet a mask, and store them temporarily in a variable. Then try to get the last three files and use for each expression again to load the data. Here is an example, I don't think is the most elegant solution, but it can help to achieve what you want:

SET vPathList = '';

// Get files current directory store them in a variable separated by comma. For each statement return all files in ascending order. Because of the order in which values are concatenated the first in the list is the most recent date

For Each file in FileList('Directory\sales*.xlsx')

  Set vPathList = $(file),$(vPathList);

NEXT file

//Create a record per file found

FileList:

LOAD

  RowNo() as ID,

  SubField('$(vPathList)', ',') as File

AutoGenerate(1);

//Get the last three files and concatenate them, add " ' " to enclose each path

TopThreeFiles:

LOAD

  chr(39) & Concat(File, chr(39)&','&chr(39)) & chr(39) as List

Resident FileList

Where ID < 4;

LET vLoadFiles = Peek('List', 0, 'TopThreeFiles');

DROP Tables FileList, TopThreeFiles;

//Load Content of the last three files

FOR Each a in $(vLoadFiles)

  //Add your load sentences here

NEXT

------

Regards,

-- Karla