topic Re: Order by clause in qlikview in QlikView

Order by clause in qlikview

tripatirao — Tue, 06 Oct 2015 11:30:05 GMT

Hi all

Can any one tell me how order by clause used below

temp:

LOAD

Currency,

From,

Rate

FROM

[Data\Data.xlsx]

(ooxml, embedded labels, table is Ex_Rate);

NoConcatenate

Xrate:

load

Currency,

From as Start,

if(Currency=Previous(Currency),Date(num(Previous(From))-1),Date(today())) as End,

Rate

Resident temp

order by Currency, From desc;

drop Table temp;

Thanks

Re: Order by clause in qlikview

MK_QSL — Tue, 06 Oct 2015 11:37:05 GMT

Looks like you have Rate available for some Currency and you want to generate Range..

Currency Date Rate

USD 01/05/2015 1.2

USD 25/08/2015 1.3

The final output required is

Currency FromDate ToDate Rate

USD 01/08/2015 24/08/2015 1.2

USD 25/08/2015 06/10/2015 1.3

if(Currency=Previous(Currency),Date(num(Previous(From))-1),Date(today()))

Resident Table is loaded with decreasing Dates

This expression will check thet Previous Currency and current row currency is same then

it will generate toDate by removing 1 day from Date... otherwise we will assume that the rate is valid till today

Re: Order by clause in qlikview

tripatirao — Tue, 06 Oct 2015 11:52:45 GMT

Hi manish

your are correct, i am generating range based on rate available for some currency.

If I understood correctly, in my script first (From) field will be loaded decreasingly then End will be calculated .

is it correct.

Thanks

Re: Order by clause in qlikview

Peter_Cammaert — Tue, 06 Oct 2015 11:59:20 GMT

Yes, that's correct. ORDER BY will be executed first (on the entire table, not just on the field to order by), before any calculations are performed by the LOAD.

I suspect that QV does this on a remporary resident table, before the actual LOAD is executed which then will use the oordered table as an intermediate source.

The same thing happens with a JOIN but the other way around: first the LOAD is executed, an intermediate table is created containing the result and only then the latter is joined to the target table.

Peter