Order by clause in qlikview

[tripatirao]

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

[MK_QSL]

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

Like

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

[tripatirao]

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

[Peter_Cammaert · Accepted solution]

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