Load qvd by timestamp in script

[markgraham123]

Hi,

I'm working on areq. where there were 20-30 Qvd files with same name but diff. time stamp.

YYYYMMDDFIlename.Qvd

Need to select the latest Qvd for the reload.

Can anyone pls help me in loading the latest qvd dynamically.

[sunny_talwar]

May be this:

Table:

LOAD FileName() as File,

          Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM

[*Filename.qvd] (qvd);

Max:

LOAD Max(Date) as MaxDate

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, Max;

FinalLoad:

LOAD *

FROM $(vMaxDate)&'Filename.qvd (qvd);

This might not be perfect because I have not tested it out, but the idea smay guide you in the right direction.

HTH

Best,

Sunny

[markgraham123]

Hi all,

FYI

20150708ABCDEN00.qvd

20150707ABCDEN00.qvd

20150706ABCDEN00.qvd

20150705ABCDEN00.qvd

are the filenames

[sunny_talwar]

Try this script:

Table:

First 1

LOAD FileName() as File,

  Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

MaxDate:

LOAD Max(Date) as MaxDate

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

HTH

Best,

Sunny

[MayilVahanan]

Hi

Try like this

Table:

LOAD Max(Date) as MaxDate;

LOAD Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

[sunny_talwar]

Oh so I guess you are suggesting to calculate max date in preceding load rather than doing it in a resident load. That would make sense. But are there any performance benefits to this as well??

Best,

Sunny

[markgraham123]

Hi Ramasamy,

When i try the above code, its not getting the date into Maxdate, and hence no data in $(MaxDate)...

Its failing

[sunny_talwar]

Did you try my script Mark?

Table:

First 1

LOAD FileName() as File,

  Date#(Left(FileName(), 8), 'YYYYMMDD') as Date

FROM *ABCDEN00.qvd (qvd);

MaxDate:

LOAD Max(Date) as MaxDate

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);

[markgraham123]

Hi Sunny,

Its working.

But the problem is its loading all the qvds from the past few years, and giving me the right result.

but, can we restrict it to current year and month (Dynamically) so that the load time will be less else its taking more than 200 qvds so as to select the right one.

[sunny_talwar]

If you know that the most recent version will always be Today() - x days, then you can try using this:

Date:

LOAD Num(Today() - x) as Date //where x needs to be an integer

Resident Table;

LET vMaxDate = Date(Peek('MaxDate'), 'YYYYMMDD');

DROP Tables Table, MaxDate;

FinalTable:

LOAD *

FROM $(vMaxDate)ABCDEN00.qvd (qvd);