Min Date from QVD FileBaseName

[didierodayo]

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

[Anil_Babu_Samineni]

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%

[ajsjoshua]

Hi,

check this

Load file based on timestamp

[rahulpawarb]

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

[didierodayo]

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

[jonathandienst · Accepted solution]

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