topic Autogenerate date from last date value until today in QlikView

Autogenerate date from last date value until today

Fri, 15 Apr 2011 07:49:45 GMT

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

Autogenerate date from last date value until today

Fri, 15 Apr 2011 08:39:52 GMT

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;

Autogenerate date from last date value until today

Fri, 22 Apr 2011 07:24:03 GMT

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

Autogenerate date from last date value until today

erichshiino — Fri, 22 Apr 2011 07:33:01 GMT

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,

Autogenerate date from last date value until today

Fri, 22 Apr 2011 10:16:35 GMT

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