SQL statements on QV datasets

[#1]

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

[fosuzuki · Accepted solution]

Hi Quinton,

I'm not sure if I understood your situation, but apparently you want to transform your columns into rows. To do this, you can use a crosstable.

So I guess this will work (replace all your Load statements with this):

DSTdata:

crosstable(class, availability)

SQL

SELECT  di.ds_date ,

        BCA_W as W,

        BCA_Y as Y,

        BCA_T as T,

        BCA_B as B,

        BCA_S as S,

        BCA_U as U,

        BCA_V as V,

        BCA_K as K,

        BCA_H as H,

        BCA_M as M,

        BCA_L as L,

        BCA_Z as Z,

        BCA_Q as Q,

        BCA_I as I,

        BCA_X as X,

        BCA_A as A

FROM    MY_ORACLE_TABLE

WHERE   di.ds_date  = to_date('17.05.2011','DD.MM.YYYY');

Hope this helps you.

Fernando

[#3]

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.

[fosuzuki]

OK, but what is exactly your problem in this process? Timeout on sql select, out of memory, ... ?

(just trying help you find the best solution... though I'm not an expert yet! )

Regards,

Fernando

[#5]

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.

Kind regards

Quinton