How to split a large QVD into year and Load into Qlikview to handle large volume?

[santharubban]

Hi all,

I have more than 100 million records in one of my fact tables and I am using the incremental load functionality, when I am creating qvd's <-- that is fine and it works. When creating a Datamodel, its taking more time due outer join between 3 big fact table. So,I would like to split the large QVD's and load into Qlikview. Have anyone come across same issues, please advice.

Thanks

[santharubban]

Any Advice pls

[Anonymous]

TABLE:

load * from your.qvd (qvd) where year = 2017;

store TABLE into 2017.qvd (qvd);

or use EasyQlik QViewer and delete everything except a specific year.

[Anil_Babu_Samineni]

May be helpful,

partitioned QVD | QlikView Help, Tips & Hints

And, stevedark‌ marcus_sommer‌ help you reg the same

[santharubban]

Hi Anil,

Thanks for the reply,

I have already go though this document, in this document they have mention how to split the date but they didn't tell how to pull data after splitting the partitioned data to Qlikview.

[Anonymous]

I still don't know what you exactly trying to achieve,

but let's suppose you have multiple qvds within 1 folder, you could just load them like that:

LOAD * FROM [*.qvd] (qvd);

[santharubban]

Hi Robin,

Thanks for the reply.

I would like to have kind of partition the qlikview and load them when they needed. 

[Digvijay_Singh]

Below script shared within our community only can be used to split qvd into quarter wise qvd, can be modified to make it year wise or half yearly.

//Generate a sample fact table with dates from 2010/01/01 to 2014/12/31

SampleData:

LOAD RecNo() AS ID,

  Date(MakeDate(2010,1,1) + recno() - 1) AS Date

AutoGenerate(1826);

//Get all possible quarters and lower/upper date limits

Quarters:

LOAD Year(Date) & '-Q' & ceil(Month(Date)/3) AS Quarter,

  Min(Date) AS MinDate,

  Max(Date) As MaxDate

Resident SampleData

Group By Year(Date) & '-Q' & ceil(Month(Date)/3);

//Loop table Quarters

for i = 0 to NoOfRows('Quarters') - 1

  let vQuarterName = Peek('Quarter', $(i));

  let vMinDate = Peek('MinDate', $(i));

  let vMaxDate = Peek('MaxDate', $(i));

  SampleDataByQuarter:

  NoConcatenate

  LOAD *

  Resident SampleData

  Where Date >= $(vMinDate) AND Date <= $(vMaxDate);

  STORE SampleDataByQuarter into SampleData_$(vQuarterName).qvd (qvd);

  DROP Table SampleDataByQuarter;

Next

[santharubban]

Thanks for the reply.

You have mention how to split the date ,can you pls provide me how to pull partitioned (splited) data into datamodel.

[Anonymous]

I don't know your data, so it's not possible for me to give you further advise...

You could also build a table with the qvd files you like to load:

TABLE:
LOAD * INLINE [
MYQVDS
1
2
AB
CDE
];
 
for i=1 to NoOfRows('TABLE')

LET var=Peek('MYQVDS',$(i)-1,'TABLE');
LOAD * FROM [$(var).qvd] (qvd);

NEXT