topic Min Date from QVD FileBaseName in QlikView

Min Date from QVD FileBaseName

didierodayo — Mon, 27 Feb 2017 06:03:04 GMT

Hello,

I would like to define 2 variable based on the dates at the end of the qvds below.
I am trying to define

vMinDate

Let vSpin = $(vMinDate) to Today() -1

How to arrive at the min date in this case for QVD1 and QVD2?

Location: D:\
QVD1_20170131
QVD1_20170201
QVD1_20170115
QVD1_20170116
QVD1_20170117
QVD1_20170118
QVD2_20170131
QVD2_20170201
QVD2_20170115
QVD2_20170116
QVD2_20170117
QVD2_20170118

Thanks

Re: Min Date from QVD FileBaseName

Anil_Babu_Samineni — Mon, 27 Feb 2017 06:21:21 GMT

Can you describe more? What was the intention you want to load / Save. I am sure you may need Min of Qvd1 & Qvd2. But not understand 100%

Re: Min Date from QVD FileBaseName

ajsjoshua — Mon, 27 Feb 2017 06:27:13 GMT

Hi,

check this

Load file based on timestamp

Re: Min Date from QVD FileBaseName

rahulpawarb — Mon, 27 Feb 2017 06:32:06 GMT

Hello Didier,

Hope that you are doing great!

Please refer below given sample script:

//Get the distinct file names
FileDemo:
LOAD DISTINCT
FileBaseName() AS MyFileName
FROM
Data\*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);


//Get the min. date for every QVD
FinalList:
LOAD
Left(MyFileName, Index(MyFileName,'_')-1) AS QVD,
Min(Right(MyFileName, Len(MyFileName) - Index(MyFileName,'_'))) AS Date
Resident FileDemo
Group By Left(MyFileName, Index(MyFileName,'_')-1);

You can refer the FinalList Table to get the QVD name and Min. Date by using Peek function.

P.S.: Instead of writing Left & Right function you can make use of SubField function. Left

Regards!

Rahul

Re: Min Date from QVD FileBaseName

didierodayo — Mon, 27 Feb 2017 06:37:09 GMT

Hi Anil,

Yes I want to read from those qvds every morning and store back into a different location.

I want to store with the same name but I don't to repeat what I have already saved unless the file is missing for any reason.

So I am thinkingof checking the min date and ensure I generate new qvds from the min date where the qvd is not already generated.thanks

Re: Min Date from QVD FileBaseName

jonathandienst — Mon, 27 Feb 2017 07:09:22 GMT

Some code like this will set vMinDate:

Let vMinDate = Today();

For Each vFile in FileList('D:\QVD*.qvd')

Let vDate = Right(SubField(vFile, '.', 1), 8);

Let vMinDate = RangeMin(vDate, vMinDate);

Next

To harden this code against the case that not all files conform to the nameing rules, use

Let vDate = Alt(Right(SubField(vFile, '.', 1), 8), Today());