Load last qvd

[andrespa]

Hi everyone, I have a set of historic qvd's on the following format:

...

qvd_15_08_2011

qvd_31_08_2011

...

What I want is just to load the more recent one. In this case the second one. Also, it has to fulfill the requeriment of be the last one but less than todays date. For example:

Today() = 04/03/2016

Historic qvd's:

qvd_31_03_2016

qvd_15_30_2016

qvd_29_02_2016

So even if the more recent qvd is the first one, I need to choose the last one (29/02/2016)

Any suggestions?

Thanks,

Andrés

[Joaquin_Lazaro]

HI Andres:

You can concatenate de qvd name in a variable.

Your goal is to convert the TODAY() date into the name of the last historic qvd ... I guess there would be a relations

Hope it helps

Joaquín

[kaushiknsolanki]

Hi,

Use this script.

Let vToday = MakeDate(2016,03,01);

sub DoDir (Root)

   for each Ext in 'qvd'

      for each File in filelist (Root&'\*.'&Ext)

      QVDList:

  Load *,Date(Date#(Right(QVDName,10),'DD_MM_YYYY')) as TDate;

         LOAD

            '$(File)' as Name,

            FileSize( '$(File)' ) as Size,

            FileTime( '$(File)' ) as FileTime,

            Mid('$(File)',Index('$(File)' , '\', SubStringCount('$(File)','\'))+1,Len('$(File)') - Index('$(File)' , '\', SubStringCount('$(File)','\')) - 4   ) as QVDName

         autogenerate 1;

    next File

   next Ext

end sub

call DoDir ('C:\Users\kaushik\Downloads')

Max:

Load Max(TDate) as MAX

Resident QVDList where TDate < '$(vToday)';

Let vMaxDate = peek('MAX',0,'Max');

LoadQVD:

Load Name as LoadQvd

Resident QVDList where TDate = '$(vMaxDate)';

Let vLoadQvd = Peek('LoadQvd',0,'LoadQvd');

Load *

from $(vLoadQvd)(qvd);

Let vMaxDate =;

Let vLoadQvd = ;

Drop table Max,LoadQVD,QVDList;

Regards,

Kaushik Solanki

[Joaquin_Lazaro]

Hi Andres:

vQVD = 'qvd_' & Date(Today()-1) ;

LOAD *

FROM $(vQVD ).qvd (qvd);

Joaquin

[bohravanraj]

Please Refer Below Code:

File_temp:
LOAD MakeDate(SubField(FileBaseName(),'_',2),SubField(FileBaseName(),'_',3),SubField(FileBaseName(),'_',4)) as FileDate,
FileBaseName() as Filename
From qvd_*.qvd(qvd)
Where Floor(num(MakeDate(SubField(FileBaseName(),'_',2),SubField(FileBaseName(),'_',3),SubField(FileBaseName(),'_',4)))) < Floor(num(Today()));

Max_Tab:
LOAD Max(FileDate) as Max_date
Resident File_temp;

let vMaxdate = date(Peek('Max_date',0,'Max_Tab'));

DROP Table Max_Tab;

Max_Filename:
LOAD Filename
Resident File_temp Where FileDate=$(vMaxdate);

LET vFilename = Peek('Filename',0,'Max_Filename');

DROP Table Max_Filename;

Table:
LOAD *
From $(vFilename).qvd(qvd);

[maxgro]

Directory;

FOR Each file in filelist('qvd_??_??_????.qvd')

  FileList:

  load

  '$(file)' as FileName,

  Date(Date#(keepchar(subfield(subfield('$(file)', '\', -1), '.', 1), '0123456789'), 'DDMMYYYY')) as FileDate

  AutoGenerate 1;

NEXT;

FileToLoad:

NoConcatenate First 1

Load FileName Resident FileList

where FileDate < Today() order by FileDate desc;

DROP Table FileList;

Let filetoload=Peek('FileName');

trace $(filetoload);

Qvd: Load * from $(filetoload) (qvd);

[tamilarasu]

Hi Andres,

Try,

FileName:
First 1 LOAD 
If(Date(Date#(Right(FileBaseName(),10),'DD_MM_YYYY'))<today(),Date(Date#(Right(FileBaseName(),10),'DD_MM_YYYY'))) as Date
FROM [Path\*.qvd] (qvd); 

Max:
Load Date(Max(QvdDate),'DD_MM_YYYY') as MaxDate
Resident FileName; 

Let vMax = Peek('MaxDate');
 Drop Table Max, FileName;

QVDData: 
LOAD *  
FROM [Path\qvd_$(vMax).qvd] (qvd);