Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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