topic Re: Loading files with same name concatinated with timstamp? in QlikView

Loading files with same name concatinated with timstamp?

Wed, 25 Nov 2020 16:16:04 GMT

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

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

Please help me out thanks in advance.

Re: Loading files with same name concatinated with timstamp?

Anonymous — Wed, 04 Jan 2017 08:23:06 GMT

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);

Re: Loading files with same name concatinated with timstamp?

muthukumar77 — Thu, 05 Jan 2017 07:11:34 GMT

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,

FROM

SensorData_*.xlsx

(ooxml, embedded labels, table is Sheet1)

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

DROP Table SensordataLatest;

Re: Loading files with same name concatinated with timstamp?

Thu, 05 Jan 2017 11:34:45 GMT

Thanks Payal...the code is working as expected

Re: Loading files with same name concatinated with timstamp?

oknotsen — Thu, 05 Jan 2017 12:59:45 GMT

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 .