Autogenerate date from last date value until today

[#1]

Hi all,

I'm looking for a solution to autogenerate dates (DATVVL) betwwen a last date value until today and copy last value (VALTIT) for a table of assets.

In the file joined, you will see 3 differents assets (NROTIT) with somes values, a creation date (CRETIT) and end date (DATTRM).

So for NROTIT 1 and 2, I would like to have the last value (1.29 ; 101.2) until today.

I had try with "autogenerate", and add load, but don't know how to write correctly the script.

Thanks for your help.

Laurent

[#2]

Hi ljonette ,

Use the below code, which automatically generates dates in to the CalendarDate field. Set your Min and max date fields to the vDateMin and vDateMax Variables.

Calendar:
LET vDateMin = num(addmonths(date(ToDay()),-27)); // asign your starting date field value here
LET vDateMax = Floor(MonthEnd(Today())); // asign your ending date field value here

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar1:
LOAD
TempDate AS CalendarDate
RESIDENT TempCalendar ORDER BY TempDate ASC;

[#3]

Thanks Arun for your suggestion,

I've made something you can see on the file joined that it works and I've the result I want.

Just want to know if there is an easier way to do it. Because with a dtb of more than 2000 NROTIT and history of NAV from year 1998, it takes a long (very long time) to load data.

Thanks for your help

Laurent

[erichshiino]

Hi, Laurent

If the answer helped you, please, don't forget to mark it as a valid answer so other people can rely on it and others don't come here to try to answer again

Best Regards,

[#5]

Hi Erich,

It was a good suggestion.

But my question now is: How to make the script easier and more powerfull?

Because with a database with more than 2 million lines for arround 2000 assets (NROTIT), it takes more than 6h.

With my second file attached before, I make this:

<code

NROTIT:
Load distinct NROTIT
Resident VL;

LET iNroT = NoOfRows('NROTIT');

For i = 0 To iNroT-1
LET sNroT = PEEK('NROTIT', i, 'NROTIT');

code>

Thanks