Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a requirement of splitting up of monthly QVDs into 5 days QVDs.
Like from 1st till 5th date data must be stored in one qvd, from 6th till 10th in 2nd qvd, from 11th till 15th in 3rd, from 20th till 25th in 4th and after 26th till monthend in 6th qvd.
Please suggest.
Thanks in advance.
Hi,
You can update the code as below
for i=0 to 5
Variable1=5*($(i))+1;
Variable2=If ($(i)=5,31,5*($(i)+1));
Noconcatenate
Table:
Load *
From QVDNAME
where DayField>='$(Variable1)' and DayField<='$(Variable2)';
Noconcatenate
MaxDate:
Load Max(Date_Field) as MaxDate
Resident Table;
Let vMaxDate=peek('MaxDate'); //Convert to require date format using date# and Date function
Drop table MaxDate;
Store Table into [qvd-qvdname'$(vMaxDate)'.qvd](QVD);
drop table Table;
next
If you feel data set is huge in the qvd and max take time to load then use Variable2 for i=1 to 4 and calculate maxdate only for i=5.
Regards,
Shwetha A
Hi,
for i=0 to 5
Variable1=5*($(i))+1;
Variable2=If ($(i)=5,31,5*($(i)+1));
Noconcatenate
Table:
Load *
From QVDNAME
where DayField>='$(Variable1)' and DayField<='$(Variable2)';
Store Table into QVDNAME$(i).qvd(QVD);
drop table Table;
next
Hi Shwetha,
Thanks for quick response.
I will try and let you know if its working fine.
Hi Shwethaa,
The solution is working perfectly fine. But i need the QVDs name to be included with the YYYYMMDD like below.
Can you please help on same.
1st qvd-qvdname_20200605.qvd
2nd qvd-qvdname_20200610.qvd
3rd qvd-qvdname_20200615.qvd
4th qvd-qvdname_20200620.qvd
5th qvd-qvdname_2020625.qvd
6th qvd-qvdname_20200631.qvd.
Basically the max date from each range(1st to 5th, 6th to 10th,11th to 15th,16th to 20th,21st to 25th and 26th till monthend) and store the name.
Hi,
You can update the code as below
for i=0 to 5
Variable1=5*($(i))+1;
Variable2=If ($(i)=5,31,5*($(i)+1));
Noconcatenate
Table:
Load *
From QVDNAME
where DayField>='$(Variable1)' and DayField<='$(Variable2)';
Noconcatenate
MaxDate:
Load Max(Date_Field) as MaxDate
Resident Table;
Let vMaxDate=peek('MaxDate'); //Convert to require date format using date# and Date function
Drop table MaxDate;
Store Table into [qvd-qvdname'$(vMaxDate)'.qvd](QVD);
drop table Table;
next
If you feel data set is huge in the qvd and max take time to load then use Variable2 for i=1 to 4 and calculate maxdate only for i=5.
Regards,
Shwetha A
Hi Shwetha,
Below solution worked.
Thank you so much for your help.