Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've 6 months of data in my qvd file. Now i want to generate qvd file per month.
For Eg:
If i've Jan, Feb, March, April, May, June months of data in qvd file. I want to generate one qvd file for Jan month, one for Feb, one for March ..... one for june in one application itself.
In my qvd file i've Date field with shows the data in the below format.
DateColumn
05-01-2012 00:12 |
06-01-2012 00:12 |
07-01-2012 00:12 |
.
.
.
31-06-2012 12:12:40
Hi,
Try like this
Temp:
LOAD Distinct Month(CaptureDateTime)*1 AS MonthName
FROM
QVD\RT_Full.qvd
(qvd) Order by Month(CaptureDateTime) asc;
Let vMin=Peek('MonthName',0);
Let vMax=Peek('MonthName')
FOR i=$(vMin) to $(vMax)
LET vMonth = Peek('MonthName',$(i),'Temp');
MonthWise:
LOAD Month(CaptureDateTime) AS MonthName1
FROM
QVD\RT_Full.qvd
(qvd)
Where Month(CaptureDateTime) = '$(vMonth)'
;
STORE MonthWise into Month\$(vMonth).qvd;
DROP Table MonthWise;
Next
DROP Table Temp;
Celambarasan
Ok, put this on your script:
FOR i = 1 to 6
table:
LOAD * FROM file.qvd(qvd)
where num(Month(DateColumn)) = i
STORE table INTO myfile.qvd;
drop table table;
NEXT
Hi,
Try with this
FOR i=1 to 12
let vTableName=Month(MakeDate(2011,$(i)));
$(vTableName):
LOAD
*
Resident TableName where Month(DateField)=$(i);
Store $(vTableName) into ..\QVD\$(vTableName).qvd;
DROP Table $(vTableName);
NEXT i;
Celambarasan
Hello,
Your idea is good. But for loop will repeat for 12 times even if those months are not existing. If you see below code it works perfectly but how can i get the existed count of months in a variable. B'coz i don't know how many months are existed in my qvd file. Right now it is 6 later it will be 3 or 4 or 9 or 12.
Temp:
LOAD Distinct Month(CaptureDateTime) AS MonthName
FROM
QVD\RT_Full.qvd
(qvd);
FOR i=0 to 6
LET vMonth = Peek('MonthName',$(i),'Temp');
MonthWise:
LOAD Month(CaptureDateTime) AS MonthName1
FROM
QVD\RT_Full.qvd
(qvd)
Where Month(CaptureDateTime) = '$(vMonth)'
;
STORE MonthWise into Month\$(vMonth).qvd;
DROP Table MonthWise;
Next
DROP Table Temp;
Hi,
Try like this
Temp:
LOAD Distinct Month(CaptureDateTime)*1 AS MonthName
FROM
QVD\RT_Full.qvd
(qvd) Order by Month(CaptureDateTime) asc;
Let vMin=Peek('MonthName',0);
Let vMax=Peek('MonthName')
FOR i=$(vMin) to $(vMax)
LET vMonth = Peek('MonthName',$(i),'Temp');
MonthWise:
LOAD Month(CaptureDateTime) AS MonthName1
FROM
QVD\RT_Full.qvd
(qvd)
Where Month(CaptureDateTime) = '$(vMonth)'
;
STORE MonthWise into Month\$(vMonth).qvd;
DROP Table MonthWise;
Next
DROP Table Temp;
Celambarasan
Thanks for your help. I also got the solution with below code.
Temp:
LOAD Distinct Month(CaptureDateTime) AS MonthName
FROM
RTQMain_Full.qvd
(qvd)
;
Let vCount=NoOfRows('Temp');
FOR i=0 to $(vCount)
LET vMonth = Peek('MonthName',$(i),'Temp');
MonthWise:
LOAD *
FROM
RTQMain_Full.qvd
(qvd)
Where Month(CaptureDateTime) = '$(vMonth)'
;
STORE MonthWise into Month\$(vMonth).qvd;
DROP Table MonthWise;
Next
DROP Table Temp;
But my question is which will be faster because i'm using aroung 37GB qvd file.
Hi,
You can go with your idea itself using NoOfRows().I didn't look that you have used Distinct for months i formulated the solutions that you can have same month multiple times.
Celambarasan
Thanks a lot.. But i want to know whether your code loads fastly. B'coz in where condition i'm comparing with month name and you are comparing with month number.
Hi,
Yes.Its a well known one handling number is faster that string better use Month number to increase your performance.
Celambarasan
Ok thanks to Share KT to me.