Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have an excel file with several fields and the last field is a date, i need read all the fields and create a new field that have the date of the next record, for example:
NUMERO | PASO | ESTATUS | FECHAESTA | New Field |
2000000000 | 1 | 0 | 04/03/2013 | 20/03/2013 |
2000000000 | 2 | 2 | 20/03/2013 | 22/03/2013 |
2000000000 | 3 | 9 | 22/03/2013 |
i think that this could be done with peek() but i don't know how.
could somebody help me?
thanks
regards
diego
See the front end solution attached.
use
below(FECHAESTA)
in New Field Expression
hope this helps
PFA.
Hi,
If you need this in script try like this
Temp:
LOAD NUMERO,
PASO,
ESTATUS,
FECHAESTA
FROM
[DATA.xlsx]
(ooxml, embedded labels, table is status);
Data:
LOAD
*,
Previous(FECHAESTA) AS NextDate
RESIDENT Temp
ORDER BY NUMERO DESC, PASO DESC;
Drop table Temp;
Regards,
jagan.
Hello
if i use a qvd file instead an excel file, the peek function don't work.
is there any diferences using excel file or qvd files like source data to use peek function??
it works
thanks
regards
diego
If you want to use Peek, replace Previous by Peek in Jagan's code
Temp:
LOAD NUMERO,
PASO,
ESTATUS,
FECHAESTA
FROM
[DATA.xlsx]
(ooxml, embedded labels, table is status);
Data:
LOAD
*,
Peek(FECHAESTA) AS NextDate
RESIDENT Temp
ORDER BY NUMERO DESC, PASO DESC;
Drop table Temp;
Hello
why the peek() function don't work if i read the same data from a QVD instead an excel file?
data:
LOAD NUMERO,
PASO,
ESTATUS,
FECHAESTA
FROM
DATA.xlsx
(ooxml, embedded labels, table is status);
NoConcatenate
data1:
load *
resident
data
order by ESTATUS,FECHAESTA,NUMERO asc;
data2:
load
NUMERO,
PASO,
ESTATUS,
FECHAESTA,
peek('FECHAESTA',recno(),'data1') as newfield
resident data1;
thanks
regards
diego
You missed sorting on field Paso and peek function is not correct. Try this
[data]:
LOAD * INLINE [
NUMERO,PASO,ESTATUS,FECHAESTA
2000000000,1,0,04/03/2013
2000000000,2,2,20/03/2013
2000000000,3,9,22/03/2013
];
NoConcatenate
data1:
load *
resident
data
order by NUMERO asc,PASO desc,ESTATUS,FECHAESTA;
Drop table data;
data2:
load
NUMERO,
PASO,
ESTATUS,
FECHAESTA,
peek(FECHAESTA) as newfield
resident data1;