Order by clause for QVDs

[#1]

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

[Jason_Michaelides]

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

[rwunderlich]

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

[#4]

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

[Jason_Michaelides]

I didn't know that - thanks Rob!

[#6]

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)

[Jason_Michaelides]

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...

[#8]

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;

[Jason_Michaelides · Accepted solution]

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;

[rwunderlich]

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

-Rob