topic Multiple autogenerated calendars in QlikView

Multiple autogenerated calendars

Anonymous — Tue, 13 Mar 2018 21:35:43 GMT

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;

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.

Re: Multiple autogenerated calendars

yujiyamane — Wed, 14 Mar 2018 01:01:13 GMT

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

Re: Multiple autogenerated calendars

Anonymous — Wed, 14 Mar 2018 04:26:37 GMT

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.

Re: Multiple autogenerated calendars

jonathandienst — Wed, 14 Mar 2018 04:49:46 GMT

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.