topic Re: autogenerate calendar in QlikView

autogenerate calendar

Anonymous — Tue, 13 Mar 2018 23:07:56 GMT

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;

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.

Re: autogenerate calendar

jwjackso — Wed, 14 Mar 2018 02:03:08 GMT

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

Re: autogenerate calendar

Anonymous — Wed, 14 Mar 2018 04:20:41 GMT

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.

Re: autogenerate calendar

Anonymous — Wed, 14 Mar 2018 15:17:23 GMT

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.

Re: autogenerate calendar

tamilarasu — Wed, 14 Mar 2018 15:42:14 GMT

Hi Christopher,

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

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

Re: autogenerate calendar

Anonymous — Thu, 15 Mar 2018 17:59:57 GMT

Thnak you! This helped me a lot.