Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Multiple autogenerated calendars

Hello,

I am trying to build a script that generates multiple calendars from a table given the minimun and maximun date per each row in the table. Right now I have this:

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 FinalDate

Resident Tabla;

drop Table Tabla;

RENAME Table t to Tabla;


This created a table with the amounts I need to add to each month (DepMensual) and the start and finishing dates of the calendar, that would be: StartDate and FinalDate.


I know I can autogenerate a calendar with this:


set vMaxdate=date(AddMonths(today(),+10));

set vMindate=Date(AddMonths(today(),-15));

tempCal:

Load

$(vMindate)+iterno()-1 as tempdate,

date($(vMindate)+iterno()-1) as Date,

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

autogenerate 1  while $(vMindate)+iterno()-1<=$(vMaxdate);

But I am stock thinking how to create a loop where my variables for Mxdate and Mindate change for each rowno() and IDFactura inside my table: "Tabla". I am thinking maybe a for loop would do the job, but I cannot see how to put it together.

Thank you in advance for your help.

3 Replies
Highlighted
Creator II
Creator II

What is your expected values in the tabla at the end?

Highlighted
Contributor II
Contributor II

Hi,

I am expecting to get a single table with the concatenated auto generated calendars from each row in the original "Tabla". So I should get a table with two columns, IDFactura and Month, with 240 rows (5 original rows times 48 for each row). I am working on the for loop, and so far I have this, but it gives me an error:

FOR i = 0 to NoOfRows('Tabla')

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

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

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

let vIDFactura= peek('IDFactura',i,'Tabla');

$(vNomCalendar):

Load

$(vIDFactura) as IDDep,

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

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

next i

The error is that I am missing a ")", but I dont seem to see where is missing.

Thank you.

Highlighted

Why do you need a calendar for or from each row? Surely the calendar should start at the earliest value of StartDate, and end at the maximum value FinalDate?

T_MINMAX:

LOAD Min(StartDate) as minDate,

  Max(FinalDate) as maxDate

Resident Tabla;

Let vMaxdate = Peek('maxDate');

Let vMindate = Peek('minDate');

DROP Table T_MINMAX;

Then build the calendar using this date range.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein