How to create from and to from this example?

[ecabanas]

Hi all,

I have this example. I have a table full of items with a rate and a date from.

How could I do an sript to put the end date. End date is next from less one day.

I used this script but did not work for the first line

LOAD *, 

Date(If(ItemId = Previous(ItemId), Previous(From)-1, YearEnd(Today()))) as To_

Resident Tarifas_New

Order By Proveedor_Tarifa,ItemId, From desc;

drop table Tarifas_New;

thank's all

Eduard

[stabben23 · Accepted solution]

Hi,

try With this code in script

Temp:
LOAD
rowno() as Row,
ItemId,
Provedoor_Tarifa,
From,
Tarifa
FROM
Yoursourcefile
;

MinDate:
LOAD
date(Min(From)) as From_Date,
Row,
ItemId,
Provedoor_Tarifa, 
Tarifa
Resident Temp
group by Row,ItemId,Provedoor_Tarifa,From, Tarifa;

Left JOIN Load
From_Date,
ItemId,
date(if(isnull(Previous(From_Date)) or ItemId<>Previous(ItemId) ,today()+1, Previous(From_Date)-1)) as To_Date
resident MinDate ; 

Drop Table Temp;

[olivierrobin]

Hello

the good question is :

which value do you want for the 1st line ?

if you have the answer, you can code an expression such as

if(recno()=1,Value for the 1st, your expression) as