topic Re: load last qvd in folder in QlikView

load last qvd in folder

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

Hello all,

How do i load (Qlik script) all columns from the the latest modified ,qvd from a specific folder?

I saw multiple simmilar open topics on Community, but without correct anserws.

Regards,

Andrei

Re: load last qvd in folder

vinieme12 — Tue, 27 Mar 2018 01:41:12 GMT

Load first time or Reload ?

Re: load last qvd in folder

PrashantSangle — Tue, 27 Mar 2018 04:09:35 GMT

can you elaborate little bit more??

what is your folder structure??

in which way you are storing file?

Regards

Re: load last qvd in folder

sujan24s — Tue, 27 Mar 2018 04:22:42 GMT

Load the latest file of a folder

can You check this it may be useful

Re: load last qvd in folder

vardhancse — Tue, 27 Mar 2018 06:06:52 GMT

Hi In general if we will get new files always and we need to load latest file.

We need to write script to move existing file to Archive file and then place new fiel in the current load folder:

Lets assume here are the files & folders available in load folder:

Re: load last qvd in folder

andreip21 — Tue, 27 Mar 2018 06:43:45 GMT

load first time.

this is how my folder looks.

I want to load only the last modified file (18_03_26-1600)

Re: load last qvd in folder

andreip21 — Tue, 27 Mar 2018 06:44:35 GMT

it's a folder from my local sharepoint. i want to load some columns from the last modified .qvd file.

Re: load last qvd in folder

andreip21 — Tue, 27 Mar 2018 06:47:12 GMT

each day 2 new .qvd files are appearing in this folder. when i load data, i want to load the last modified .qvd.

Re: load last qvd in folder

bramkn — Tue, 27 Mar 2018 06:51:53 GMT

Use a filelist loop to get all files in the folder and then read the modified date to determine which one you want with load first and order by modified date. This approach does not work if the files are never archived. As the reload will take longer everyday (retrieving the filelist)

Re: load last qvd in folder

andreip21 — Tue, 27 Mar 2018 06:55:46 GMT

yes, great theory, but i'm using qlik only for 2 months and i don't know all functions

Re: load last qvd in folder

bramkn — Tue, 27 Mar 2018 07:07:53 GMT

FOR Each File in FileList('<path>')

AllQVDs:

LOAD

'$(File)' AS QVD,

FileTime('$(File)') AS QVDModifiedDate

AutoGenerate(1);

Next File

NewestQVD:

FIRST 1 LOAD

QVD,

QVDModifiedDate

RESIDENT AllQVDs

ORDER BY QVDModifiedDate DESC;

DROP TABLE AllQVDs;

This will result in a table with 1 record containing the newest QVD

Use Peek or Lookup to get the value of the QVD and load from this path.

hope this was clear enough

Re: load last qvd in folder

andreip21 — Tue, 27 Mar 2018 07:51:19 GMT

all clear until peek/ lookup function

Re: load last qvd in folder

bramkn — Tue, 27 Mar 2018 08:05:06 GMT

Peek(field_name[, row_no[, table_name ] ])

So something like this:

Let vLastQVD = Peek('QVD',0,'NewestQVD');

LOAD

FROM '$(vLastQVD)' (qvd);

Re: load last qvd in folder

shmulu — Fri, 20 Dec 2019 21:57:56 GMT

Can you help me in re-writing this for the last n files? Like if it were the last 5? This is what I have so far:

FOR Each File in FileList('FileLocation')

AllQVDs:
LOAD
'$(File)' AS QVD,
FileTime('$(File)') AS QVDModifiedDate
AutoGenerate(1);
Next File

NewestQVD:
FIRST 5 LOAD //determine the newest
QVD,
vFolder & '/' & QVD & '.qvd' as QVDFileName,
QVDModifiedDate
RESIDENT AllQVDs
ORDER BY QVDModifiedDate DESC;

DROP TABLE AllQVDs;

Set vFolder = 'FileLocation';
for each file in filelist(QVDFileName)
load * from [$(vFolder)/$(QVD)/.qvd] (qvd);
next File

Re: load last qvd in folder

BhaskarSharma — Fri, 05 Apr 2024 07:17:52 GMT

It is easy when there is a timestamp in the QVD name, or the names of the QVDs are different.

What if there's no timestamp in QVD name, and all QVDs are of same name... For example, in Google Drive, you can have multiple QVDs with same name... and if you wish to read the latest QVD, how do you do that?

Regards,

Bhaskar