Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Yup, you are right, no need to drop Table Just use Drop Table Date;
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
Hi all,
FYI
20150708ABCDEN00.qvd
20150707ABCDEN00.qvd
20150706ABCDEN00.qvd
20150705ABCDEN00.qvd
are the filenames
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
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);
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
Hi Ramasamy,
When i try the above code, its not getting the date into Maxdate, and hence no data in $(MaxDate)...
Its failing
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);
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.
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);