Creating a Variable

[Sharbel]

Hello,

The following load script creates a table with dates ranging from 01/01/2019 (Minimum Date) till 31/08/2023 (Maximum Date):

Let v_MinDate = Num('01/01/2023');

Let v_MaxDate = Num('05/01/2023');

Date:

Load

Date($(v_MinDate) + (IterNo()-1)) as Date

Autogenerate 1
While $(v_MinDate) + (IterNo()-1) <= $(v_MaxDate);

In order not to manually change the maximum date  in the v_MaxDate in each consecutive month, i tried unsuccessfully  to modify the above script so it will be able to calculates the  maximum date dynamically based on the following Transaction Table: 

Any Ideas?

Regards,

Sharbel

[rubenmarin]

Hi, in case it helps I usually use a calendar that reads the values stored in the reference date field (quicker than read the data table) and creates all dates between the min and max values of that field:

LOAD 
  DateField  as Date,
;
LOAD 
  MinDate+ IterNo()-1  as DateField
While MinDate+ IterNo()-1 <= MaxDate
;
LOAD 
  Floor(Min(FieldValue('DateField', RecNo()))) as MinDate,
  Floor(Max(FieldValue('DateField', RecNo()))) as MaxDate
AutoGenerate FieldValueCount('DateField')
;