Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Loading files with same name concatinated with timstamp?

Hi There,

I am new to Qlikview and try to load file according to the timestamp. The file provider is sending files with similar name including timestamp it them(given below).

SensorData_YYYYMMDDHHMMSS.csv

SensorData_YYYYMMDDHHMMSS.csv


I just want to load the file which have latest timestamp.


Please help me out thanks in advance.



Tags (1)
4 Replies
Highlighted
payalgosar
Contributor II

Re: Loading files with same name concatinated with timstamp?

Hi,

Hope this is helpful to u

FileNames:

LOAD Distinct FileName() as FileName,

     Timestamp( Timestamp#( mid(FileBaseName(),  Index(FileBaseName(), '_')+1, len(FileBaseName())),'YYYYMMDDhhmmss')) as DT_Time

FROM SensorData*.csv

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Left Join (FileNames)

LOAD Timestamp(Max(DT_Time)) as DT_Time, '1' as Flag

Resident FileNames;

FileToLoad:

LOAD FileName

Resident FileNames where Flag = 1;

Let vFileName = Peek('FileName', 0, 'FileToLoad');

DROP Table FileNames;

DROP Table FileToLoad;

Data:

LOAD *

FROM $(vFileName)

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Partner
Partner

Re: Loading files with same name concatinated with timstamp?

Hi,

I hope it will useful,

//File Names SensorData_20160101090000 and SensorData_20160101100000

SensordataLatest:

LOAD Max(FileTimeStamp) as MaxTimeStamp;

LOAD Subfield(Subfield(FileName(),'_',2),'.',1) as FileTimeStamp

FROM

SensorData_*.xlsx

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD A,

     B,

     C

FROM

SensorData_*.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Exists(MaxTimeStamp,Subfield(Subfield(FileName(),'_',2),'.',1));

DROP Table SensordataLatest;

Muthukumar Pandiyan
Highlighted
Not applicable

Re: Loading files with same name concatinated with timstamp?

Thanks Payal...the code is working as expected

Highlighted
oknotsen
Honored Contributor III

Re: Loading files with same name concatinated with timstamp?

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!