topic Re: Latest 3 Records in QlikView

Latest 3 Records

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

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?

Re: Latest 3 Records

avinashelite — Thu, 28 Apr 2016 06:09:28 GMT

check this thread you will get the idea

how to Load only the latest files?

Re: Latest 3 Records

tamilarasu — Thu, 28 Apr 2016 06:17:08 GMT

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

Re: Latest 3 Records

MK_QSL — Thu, 28 Apr 2016 06:20:05 GMT

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;

Re: Latest 3 Records

ajsjoshua — Thu, 28 Apr 2016 06:22:54 GMT

Hi,

check thisQlikView - Loading Data From Multiple Files - YouTube

Re: Latest 3 Records

Anonymous — Thu, 28 Apr 2016 06:40:24 GMT

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

------

Regards,

-- Karla