topic Load last qvd in QlikView

Load last qvd

andrespa — Fri, 04 Mar 2016 09:25:35 GMT

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

Re: Load last qvd

Joaquin_Lazaro — Fri, 04 Mar 2016 09:41:26 GMT

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

Re: Load last qvd

kaushiknsolanki — Fri, 04 Mar 2016 09:46:19 GMT

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

Re: Load last qvd

Joaquin_Lazaro — Fri, 04 Mar 2016 09:47:31 GMT

Hi Andres:

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

LOAD *

FROM $(vQVD ).qvd (qvd);

Joaquin

Re: Load last qvd

bohravanraj — Fri, 04 Mar 2016 09:50:22 GMT

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

Re: Load last qvd

maxgro — Fri, 04 Mar 2016 09:53:50 GMT

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

Re: Load last qvd

tamilarasu — Fri, 04 Mar 2016 09:58:58 GMT

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