I'm new to Qlikview and need some basics to get started.
I have a large dataset which I need to bring in and perform a cartesian join on to extrapolate the dataset. I can't perform the extrapolation in Oracle as the dataset is too large. My options are to break the dataset down into manageable chunks but I'm sure that Qlikview would be able to do the query on the dataset - I just need to understand how.
So far I have the following dataset as a QVD :
/* Creates 16 rows of data / 1 column having the no.s 1 to 16 */
SELECT rownum r from DUAL CONNECT BY LEVEL <= 16;
Replace Store DS1 into C:\ProgramData\QlikTech\Documents\DSt1.qvd;
/* Creates large dataset */
SELECT di.ds_date ,
WHERE di.ds_date = to_date('17.05.2011','DD.MM.YYYY');
Replace Store DS2 into C:\ProgramData\QlikTech\Documents\DSt2.qvd;
/* Cartesian Join Datasets */
DSq: LOAD * FROM C:\ProgramData\QlikTech\Documents\DSt2.qvd (qvd); JOIN LOAD * FROM C:\ProgramData\QlikTech\Documents\DSt1.qvd (qvd);
Replace Store DSq into C:\ProgramData\QlikTech\Documents\DSt3.qvd;
/* Restructure the Dataset */
if(R=16,'A','9')))))))))))))))) as CLASS,
if(R=16,BCA_A,'9'))))))))))))))))) as AVAILABILITY
FROM C:\ProgramData\QlikTech\Documents\DSt3.qvd (qvd);
It's the last step here that I would like to able to perform using QV scripting - is there a way of doing this without having to build my datasets incrementally to QVD files. I'm new to QV and therefore it would be good to understand how to Query QVD or flat files efficiently.
Here's my original SQL statement that I'm trying to recreate.
Thanks Fernando – this is exactly the solution however it cannot handle the amount of data I’m trying to crosstable. I can achieve the desired result by writing each data manipulation out to a file in stages. I tried to add this comment but both IE8 and Firefox are crashing when updating the page.
It’s an out of memory issue. It’s running on a 16Gb server (windows x64) but it fails at 4Gb. It’s an 80 million row dataset though and therefore I think this is too much for a crosstable as it has to perform the operation in memory rather than writing to a file. The full dataset I wish to crosstable is much larger and therefore I believe that my only option is to create this in stages writing to qvd files.
Thanks for your interest though - if there is another method then I would still be interested.