You try this:
LET vMesInicial = Date('01/01/2014'); LET vMesFinal = Date(MonthEnd(Today())-2); DATES: LOAD Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMMDD') AS DiaIni, Date(MonthEnd(AddMonths('$(vMesInicial)',RowNo()-1)),'YYYYMMDD') AS DiaFim, Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMM') AS AnoMes AutoGenerate(Ceil((vMesFinal-vMesInicial)/30.5)); //EXIT Script; FOR D=1 TO (NoOfRows('DATES')) LET vAnoMes = Peek('AnoMes',$(D)-1); LET vDiaIni = Peek('DiaIni',$(D)-1); LET vDiaFim = Peek('DiaFim',$(D)-1); TRACE $(vAnoMes); [Table]: SQL Select * from Table where MonthYear = '$(vAnoMes)'; Store [Table] into DirExtraidos\Table_$(vAnoMes).QVD; Drop table [Table]; NEXT DROP Table DATES;
Do you have chage this part of the script for your script.
[Table]: SQL Select * from Table where MonthYear = '$(vAnoMes)'; Store Table into DirExtraidos\Table_$(vAnoMes).QVD; Drop Table;
[Your Table Name]: SQL Select * from YourTableName where YourFildWithPeriod = '$(vAnoMes)'; Store [Your Table Name] indo [Your Table Name]_$(vAnoMes).qvd; Drop [Your Table Name];
generally, I would always try to use as few distinct elements as possible - so I would suggest not using Notepad or any other external tool, but only the qvd files themselves.
- So every qvd should hold five months' worth of data, yes?
- I assume you already have created that archive_qvd that goes up to 201310, yes?
=> Then my suggestion would be this:
- Find out how to load the file_names of all the files in the directory where those qvd files should be (unfortunately I don't know how to do that, but (not ideally, but alternatively) you might use your .txt approach there, as a qvs file to be populated by the customer and as a last resort you can always hard-code them in an Ínline table)
- Get the largest second ("to") year-month figure from all of those files
- Compare that to the current year-month
- If the current year-month is still within the scope of that file, you can use it
=> the new data should be appended to that qvd
- If the current year-month is not in the scope of that file anymore, you have to create a new one
=> First, LOAD RESIDENT (from some other table, doesn't matter) an empty table, just to have the fields
=> then append the new data to that
I had tried my level best, just by filtering date in the table.
I had created From and To notepad, in From, start date, in To end date,
Asked user to modify the date once they are updating db three months once.
I load tat notepad in QV, and set it as variable,
Then in where condtion
where Date>=$(vFrom) and Date<=$(vTo)
and store it as
I take all the qvd by using * sign after the table name.
Load * From Table_*.qvd;
so this will read all the data from the beginning.
Thanks to everyone who had replied to my discussion.