# SQL statements on QV datasets

**QuintonDavies**May 26, 2011 4:52 AM

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 */

DS1:

SQL

SELECT rownum r from DUAL CONNECT BY LEVEL <= 16;

Replace Store DS1 into C:\ProgramData\QlikTech\Documents\DSt1.qvd;

/* Creates large dataset */

DS2:

SQL

SELECT di.ds_date ,

BCA_W,

BCA_Y,

BCA_T,

BCA_B,

BCA_S,

BCA_U,

BCA_V,

BCA_K,

BCA_H,

BCA_M,

BCA_L,

BCA_Z,

BCA_Q,

BCA_I,

BCA_X,

BCA_A

FROM MY_ORACLE_TABLE

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 */

DSTdata:

LOAD DS_DATE,

round(R),

if(R=1,'W',

if(R=2,'Y',

if(R=3,'T',

if(R=4,'B',

if(R=5,'S',

if(R=6,'U',

if(R=7,'V',

if(R=8,'K',

if(R=9,'H',

if(R=10,'M',

if(R=11,'L',

if(R=12,'Z',

if(R=13,'Q',

if(R=14,'I',

if(R=15,'X',

if(R=16,'A','9')))))))))))))))) as CLASS,

round(if(R=1,BCA_W,

if(R=2,BCA_Y,

if(R=3,BCA_T,

if(R=4,BCA_B,

if(R=5,BCA_S,

if(R=6,BCA_U,

if(R=7,BCA_V,

if(R=8,BCA_K,

if(R=9,BCA_H,

if(R=10,BCA_M,

if(R=11,BCA_L,

if(R=12,BCA_Z,

if(R=13,BCA_Q,

if(R=14,BCA_I,

if(R=15,BCA_X,

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.

SELECT ds_date,

CLASS,

COALESCE(W,Y,T,B,S,U,V,K,H,M,L,Z,Q,I,X,A) availability

FROM (

SELECT di.ds_date ,

CASE WHEN S.R = 1 THEN 'W'

WHEN S.R = 2 THEN 'Y'

WHEN S.R = 3 THEN 'T'

WHEN S.R = 4 THEN 'B'

WHEN S.R = 5 THEN 'S'

WHEN S.R = 6 THEN 'U'

WHEN S.R = 7 THEN 'V'

WHEN S.R = 8 THEN 'K'

WHEN S.R = 9 THEN 'H'

WHEN S.R = 10 THEN 'M'

WHEN S.R = 11 THEN 'L'

WHEN S.R = 12 THEN 'Z'

WHEN S.R = 13 THEN 'Q'

WHEN S.R = 14 THEN 'I'

WHEN S.R = 15 THEN 'X'

WHEN S.R = 16 THEN 'A'

END CLASS,

CASE WHEN S.R = 1 THEN BCA_W END W,

CASE WHEN S.R = 2 THEN BCA_Y END Y,

CASE WHEN S.R = 3 THEN BCA_T END T,

CASE WHEN S.R = 4 THEN BCA_B END B,

CASE WHEN S.R = 5 THEN BCA_S END S,

CASE WHEN S.R = 6 THEN BCA_U END U,

CASE WHEN S.R = 7 THEN BCA_V END V,

CASE WHEN S.R = 8 THEN BCA_K END K,

CASE WHEN S.R = 9 THEN BCA_H END H,

CASE WHEN S.R = 10 THEN BCA_M END M,

CASE WHEN S.R = 11 THEN BCA_L END L,

CASE WHEN S.R = 12 THEN BCA_Z END Z,

CASE WHEN S.R = 13 THEN BCA_Q END Q,

CASE WHEN S.R = 14 THEN BCA_I END I,

CASE WHEN S.R = 15 THEN BCA_X END X,

CASE WHEN S.R = 16 THEN BCA_A END A

FROM MY_ORACLE_TABLE a, /* filtered by a date range */

(select rownum r from DUAL CONNECT BY LEVEL <= 16) s