5 GB Flat file around 2Cr. records want to create QVD

[#1]

Dear All,

i have a 5 GB of flat file and around 2 cr records i want create a QVD of that. doing a basis load and creating QVD its taking lots of time and memory. kindly suggest and better or optimized way to do this.

Thanks in Advance

JKV

[sushil353]

Hi Jitendra,

Creating QVD and saving it on drive will take some time but is also depend on your machine hardware configuration.

if you think that you have sufficient amount of hardware then go through below link

qlikviewcookbook.com/2011/03/document-compression/

HTH

Sushil

[jagan]

Hi,

For 5 GB data to load and store into QVD it will definitely take lot of time, it all depends on the Hardware resources.  If your machine has huge RAM and good configuration then it will take very less time.

First of all try to reduce the records, based on the following questions

- Are you really using all the records?  For example in your table if you have 20 years of data are you using entire data for your analysis, if not restrict the data by date.

- Are you using all the fields in the table?  If not remove the fields which are not used.

This way we can reduce the QVD size.

Hope this helps you.

Regards,

Jagan.

[SunilChauhan]

if you have timestamp or date record created . you could break you data on the basis of time stamp

and store data yearly suppose 5 years data .then you could break it into 5 Qvd's

Data2011.qvd

Data2012.qvd

Data2013.qvd

Data2014.qvd

Data2015.qvd

other wise you could also break you data on the basis of cirtain field

like

Department if you have limited department for example: if you have Admin,It,HR,sales.then

Data_Sales.qvd

Data_adminqvd

Data_HR.qvd

Data_x1.qvd

Data_x2.qvd

these qvd's can also be reduced by analysis fields used for analysis and not. you could remove fields which are not used in analysis or dashboards

hope this helps

[Anonymous · Accepted solution]

Hi Jitendra,

Try to break QVDs yearwise,use below code in script:

//Yearly QVD:

TempTable:
LOAD Distinct Year(Date) AS Year
FROM QVD\Main.qvd(qvd)
Order by Year(Date) asc;

NoConcatenate

Table:
Load Year,Min(Year) as minYear, max(Year) as maxYear
Resident TempTable
Group By Year;

Let vMin=Peek('minYear', 0, 'Table');
Let vMax=Peek('maxYear', 0, 'Table');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek('Year',$(i),'Table');

YearWise:
NoConcatenate
LOAD * FROM QVD\Main.qvd(qvd)
Where Year(Date) = '$(vYear)';

STORE YearWise into TableName_$(vYear).qvd;
DROP Table YearWise;

Next

DROP Table TempTable;
DROP Table Table;

Regards

Neetha