autogenerate calendar

[Anonymous]

Hi again,

I am still working on my automatic generated calendar for each row on my table, I am trying to build the calendar with the for, but I keep getting an error, this is the script:

TablaDep:

LOAD * INLINE [

    TipoDep, MesesDep

    Auto, 48

    Otros, 120

];

Tabla:

LOAD * INLINE [

    IDFactura, TipoDep, Monto, Fecha

    C64079E0-A73C-4A7D-A0A6-C21636E18864, Auto, 10000,Ene 2018

    31848A13-6189-4E61-95BD-775C2BDFF296, Auto, 20000,Feb 2017

    C733D223-C797-4DE9-B3A2-B9B207E833E7, Auto, 50000,Mar 2018

    E7EBD819-3311-4145-9B6C-19DC3AE39C01, Auto, 60000,Jun 2016

    F85A59CE-F3B2-4068-9A52-D4829B9B93CF, Auto, 30000,Jun 2016

];

left join

load

TipoDep,

MesesDep

Resident TablaDep;

drop Table TablaDep;

t:

load*,

RowNo() as DepNum,

Monto/MesesDep as DepMensual,

Date#(Fecha,'MMM YYYY') as StartDate,

monthname(addmonths(Date#(Fecha,'MMM YYYY'),+MesesDep)) as EndDate

Resident Tabla;

drop Table Tabla;

RENAME Table t to Tabla;

FOR i = 0 to NoOfRows('Tabla')

let vStartDate= peek('StartDate',i,'Tabla');

let vEndDate= peek('EndDate',i,'Tabla');

let vNomCalendar= peek('DepNum',i,'Tabla');

$(vNomCalendar):

Load

Monthname($(vStartDate),+iterno()-1) as MonthYear

autogenerate 1  while $(vStartDate)+iterno()-1<=$(vEndDate);

next i

EXIT Script;

The error I keep getting is that I am missing a ")", could you please help me?

Thank you.

[jwjackso]

On the first pass through the loop, vEndDate is Null.

FOR i = 0 to NoOfRows('Tabla')

let vStartDate= peek('StartDate',i,'Tabla');

let vEndDate= peek('EndDate',i,'Tabla');

let vNomCalendar= peek('DepNum',i,'Tabla');

$(vNomCalendar):

Load

Monthname($(vStartDate),+iterno()-1) as MonthYear

autogenerate 1  while $(vStartDate)+iterno()-1<=$(vEndDate);

next i

[Anonymous]

Hi,

I think you see it as a Null because of the variables I am using, because my months are in Spanish, I am guessing that is you change the "Tabla" first row from "Ene 2018" to "Jan 2018" it will show all the right values.

[Anonymous]

I though about doing that in the beginning, but I need to have the end and start date flagged inside the table for each row, because each row is a different calculation, that means that it have have different Amount and different end and start dates.

If were to use a single calendar, I don't see how could I separate the values per register.

[tamilarasu · Accepted solution]

Hi Christopher,

I am not sure about your expectation but I can help you to solve the error message.

t:

load*,

RowNo() as DepNum,

Monto/MesesDep as DepMensual,

Date(Date#(Fecha,'MMM YYYY'),'MMM YYYY') as StartDate,

monthname(addmonths(Date#(Fecha,'MMM YYYY'),MesesDep)) as EndDate

Resident Tabla;

drop Table Tabla;

RENAME Table t to Tabla;

FOR i = 0 to NoOfRows('Tabla')-1

let vStartDate= Num(peek('StartDate',i,'Tabla'));

let vEndDate= Num(peek('EndDate',i,'Tabla'));

let vNomCalendar= peek('DepNum',i,'Tabla');

NoConcatenate   // Remove this line if you want single table (i.e Table name 1)

$(vNomCalendar):

Load Distinct

Monthname($(vStartDate)+iterno()-1) as MonthYear

autogenerate 1  while $(vStartDate)+iterno()-1 <= $(vEndDate);

next i

[Anonymous]

Thnak you! This helped me a lot.