topic Re: How to split a huge qvd file into multiple qvds in App Development

How to split a huge qvd file into multiple qvds

FranciscoQ — Wed, 11 May 2022 13:27:16 GMT

Hi, I have a qvd with 15 years of sales data so far, it keeps storing data, and I would like to process the qvd and split it into multiple qvds, and each qvd having 3 years of data.

I already found a way to split the qvd into yearly qvds. Each qvd has a different year:

SALES_YEAR:
LOAD
min(YEAR) as MinYEAR,
max(YEAR) as MaxYEAR
FROM ......;

LET minyear= PEEK('MinYEAR',0,'SALES_YEAR');
LET maxyear= PEEK('MaxYEAR',0,'SALES_YEAR');

DROP TABLE SALES_YEAR;

for

vyear = $(minyear) to $(maxyear);

SALES:
LOAD
*
FROM .....

where YEAR = $(vyear);

Store SALES into [lib://......../SALES_$(vyear).qvd](qvd);
drop table SALES;

That worked perfect, but I can't think a solution in order to have each qvd containing 3 years.

2010-2012, 2013-2015,........

I would appreciate some help,

Regards,

Francisco.

Re: How to split a huge qvd file into multiple qvds

micheledenardi — Wed, 11 May 2022 14:30:40 GMT

For the future I highly suggest you to read this thread: HOW TO SEGMENT QVD FILES

Then... this approach works:

InitalQVD: NoConcatenate Load YEAR, rand() as Sales Inline [ YEAR 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022]; DistinctYears: Load Concat(Chr(39)&YEAR&chr(39),',') as YEARS Group by BlockSize; LOAD Distinct YEAR, Ceil(RecNo(),3) as BlockSize Resident InitalQVD Order by YEAR asc; For i=0 to NoOfRows('DistinctYears')-1 let vYears=peek('YEARS',i,'DistinctYears'); let vTableName=replace(Replace('$(vYears)',',','_'),Chr(39),''); '$(vTableName)': NoConcatenate Load * Resident InitalQVD Where Match(YEAR,$(vYears)); Store '$(vTableName)' into '$(vTableName)'.qvd(qvd); Drop Table '$(vTableName)'; Next Drop Table DistinctYears; Drop Table InitalQVD;

Re: How to split a huge qvd file into multiple qvds

Andrei_Cusnir — Wed, 11 May 2022 14:49:15 GMT

Hello,

The approach shared by micheledenardi is very good and I recommend taking a look at it. However, if you are looking for a way to modify the script that you already have, so that you could generate the QVDs based on 3 year ranges, then you can use step in your For..Next statement.

Here is the example of the modified script:

FOR vyear = $(minyear) to $(maxyear) step 3

Let toYear = $(vyear) + 2;
Trace Store data from: $(vyear) to $(toYear);

Data:
LOAD
Year,
Value
FROM [lib://Save QVD/ENTIRE.qvd]
(qvd)
where Year >= $(vyear) and Year <= $(toYear);

Store Data into [lib://Export/DATA_$(vyear)_to_$(toYear).qvd](qvd);
drop table Data;

Next;

As you can see, this script is going every 3 years by using the "step" within the statement and now you just need to create within the loop a new variable that will calculate the end year. So if you start on 2000 the variable will count +2 making the end year be "2002". This will allow you to select years 2000, 2001 and 2002. After that the step will update the vyear variable by 3. So the next 3 years will be 2003, 2004 and 2005 etc.

This is the output that I get:

Re: How to split a huge qvd file into multiple qvds

FranciscoQ — Wed, 11 May 2022 17:00:13 GMT

That solution is wonderfull, thank you very much!