topic Order by clause for QVDs in QlikView

Order by clause for QVDs

Mon, 09 Apr 2012 17:44:50 GMT

Hello

I have the following script which loads a QVD:

CONSOLETABLE:

LOAD NombreCajero,

Consola,

Corregido,

FechaInicio,

FechaFin,

TiempoEventosMins,

Eventos,

DiaEvento,

MesEvento,

AñoEvento,

AñoMesEvento

FROM

(qvd);

I tried adding "order by AñoEvento" but I get an error.

So how can I order the resultset by a column??

Regards

Order by clause for QVDs

Jason_Michaelides — Mon, 09 Apr 2012 19:27:41 GMT

Where did you add the Order By exactly?

CONSOLETABLE:

LOAD NombreCajero,

Consola,

Corregido,

FechaInicio,

FechaFin,

TiempoEventosMins,

Eventos,

DiaEvento,

MesEvento,

AñoEvento,

AñoMesEvento

FROM

(qvd)

order by AñoEvento;

should work fine.

Hope this helps,

Jason

Order by clause for QVDs

rwunderlich — Mon, 09 Apr 2012 20:03:45 GMT

ORDER BY only works when reading from a resident table. If you need the order by, first load the QVD and then load the resident with order by. Be sure you use NOCONATENATE to prevent automatic concatenation.

CONSOLETABLE:

LOAD * FROM CONSOLTABLE.qvd (qvd);

CONSOLETABLE2:

NOCONCATENATE

LOAD * RESIDENT CONSOLETABLE ORDER BY AnoEvrento;

DROP TABLE CONSOLETABLE;

-Rob

http://robwunderlich.com

Order by clause for QVDs

Mon, 09 Apr 2012 20:06:56 GMT

I added it right there, where you suggested...and got the following error:

Order by clause for QVDs

Jason_Michaelides — Mon, 09 Apr 2012 20:14:35 GMT

I didn't know that - thanks Rob!

Order by clause for QVDs

Mon, 09 Apr 2012 20:54:56 GMT

Thanks Rob

Probably your approach will load (read) the data twice and would take little bit more time to load the whole thing.

So I guess, I should make the Order by on the original script that loads the data from data base into the QVD.

The problem is that, when doing Incremental Load, the appended records seem to be inserted at the begining of the QVD and that makes de SORT BY in charts, to display recent loaded data first (having April data before January data)

Order by clause for QVDs

Jason_Michaelides — Mon, 09 Apr 2012 21:28:08 GMT

In an incremental load you have to load the original QVD in then append the new data to it before storing the whole thing again. I suspect you just have your CONCATENATE() statement reversed...

Order by clause for QVDs

Mon, 09 Apr 2012 21:44:50 GMT

This is how my Incremental Load Script looks like...is it OK???

ODBC CONNECT TO [ATMs SQL server] (XUserId is UeNCSBFIKaXGVCRJdB, XPassword is EXOAfBFIKaXGVCRJfA);

CONSOLETABLE:
LOAD
NombreCajero,
Consola,
    Corregido,
    FechaInicio,
    FechaFin,
    TiempoEventosMins,
    Eventos,
    Day(FechaInicio) as DiaEvento,
    Month(FechaInicio) as MesEvento,
    Year(FechaInicio) as AñoEvento,
    YEAR(FechaInicio)&'-'&Month(FechaInicio) as AñoMesEvento
    ;
SQL SELECT NombreCajero,
CASE
WHEN Consola=1 THEN 'No Cont'
WHEN Consola=2 THEN 'M Sup'
WHEN Consola=3 THEN 'F Fisica'
WHEN Consola=4 THEN 'Sin Efec'
ELSE 'NA'
END as Consola,
    Corregido,
    FechaInicio,
    FechaFin,
    CASE
WHEN DateDiff(minute,FechaInicio,FechaFin) <30.00 and Consola =1 THEN 0
ELSE DateDiff(minute,FechaInicio,FechaFin)
END as TiempoEventosMins,
    CASE
WHEN DateDiff(minute,FechaInicio,FechaFin) <30.00 and Consola =1 THEN 0
ELSE 1
END as Eventos
FROM Tracker.dbo.ConsoleTable with(nolock)
where FechaInicio>$(MaxID)
order by Year(FechaInicio), Month(FechaInicio);

Concatenate LOAD NombreCajero,
     Consola,
     Corregido,
     FechaInicio,
     FechaFin,
     TiempoEventosMins,
     Eventos,
     DiaEvento,
     MesEvento,
     AñoEvento,
     AñoMesEvento
FROM
CONSOLETABLE.qvd
(qvd);

STORE CONSOLETABLE INTO CONSOLETABLE.QVD;

Order by clause for QVDs

Jason_Michaelides — Mon, 09 Apr 2012 22:13:08 GMT

Try this:

ODBC CONNECT TO [ATMs SQL server] (XUserId is UeNCSBFIKaXGVCRJdB, XPassword is EXOAfBFIKaXGVCRJfA);

CONSOLETABLE:

LOAD NombreCajero,

Consola,

Corregido,

FechaInicio,

FechaFin,

TiempoEventosMins,

Eventos,

DiaEvento,

MesEvento,

AñoEvento,

AñoMesEvento

FROM

CONSOLETABLE.qvd

(qvd);

Concatenate

LOAD
NombreCajero,
Consola,
    Corregido,
    FechaInicio,
    FechaFin,
    TiempoEventosMins,
    Eventos,
    Day(FechaInicio) as DiaEvento,
    Month(FechaInicio) as MesEvento,
    Year(FechaInicio) as AñoEvento,
    YEAR(FechaInicio)&'-'&Month(FechaInicio) as AñoMesEvento
    ;
SQL SELECT NombreCajero,
CASE
WHEN Consola=1 THEN 'No Cont'
WHEN Consola=2 THEN 'M Sup'
WHEN Consola=3 THEN 'F Fisica'
WHEN Consola=4 THEN 'Sin Efec'
ELSE 'NA'
END as Consola,
    Corregido,
    FechaInicio,
    FechaFin,
    CASE
WHEN DateDiff(minute,FechaInicio,FechaFin) <30.00 and Consola =1 THEN 0
ELSE DateDiff(minute,FechaInicio,FechaFin)
END as TiempoEventosMins,
    CASE
WHEN DateDiff(minute,FechaInicio,FechaFin) <30.00 and Consola =1 THEN 0
ELSE 1
END as Eventos
FROM Tracker.dbo.ConsoleTable with(nolock)
where FechaInicio>$(MaxID)
order by Year(FechaInicio), Month(FechaInicio);

STORE CONSOLETABLE INTO CONSOLETABLE.QVD;

Order by clause for QVDs

rwunderlich — Tue, 10 Apr 2012 04:02:38 GMT

Can you sort your charts by Date instead of Load Order?

-Rob