topic How to create from and to from this example? in QlikView

How to create from and to from this example?

ecabanas — Thu, 05 Apr 2018 08:54:22 GMT

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

Re: How to create from and to from this example?

stabben23 — Thu, 05 Apr 2018 09:41:28 GMT

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;

Re: How to create from and to from this example?

olivierrobin — Thu, 05 Apr 2018 09:42:57 GMT

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