Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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());
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%
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
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
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());