Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any Advice pls
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.
May be helpful,
partitioned QVD | QlikView Help, Tips & Hints
And, stevedark marcus_sommer help you reg the same
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.
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);
Hi Robin,
Thanks for the reply.
I would like to have kind of partition the qlikview and load them when they needed.
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
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.
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