topic Re: Add rows in table between start and enddate in QlikView

Add rows in table between start and enddate

giovanni16 — Fri, 06 Feb 2015 20:30:17 GMT

Hi all,

Can someone help me with the right script for creating/adding rows in table based on start and end date.

I want to have a row for each record from the start date till the end date for the number of months. So I can create a table of the monthvalues for each contract record in time.

It is probably simple, but as a beginner, any help will be appreciated.

Thank you.

Re: Add rows in table between start and enddate

maxgro — Fri, 06 Feb 2015 20:47:42 GMT

PFA

and also read this

Generating Missing Data In QlikView

Re: Add rows in table between start and enddate

giovanni16 — Fri, 06 Feb 2015 21:03:59 GMT

Massimo,

Thank you for your answer.

However, what I want to achieve is to have one row for each month, instead of for each day. I tried to change the amounts in amount per day, but it gives a difference in the months. Tried to change the Date() into Month(), but with no result.

Is it possible to create only the rows for every first day of the month?

Thank you.

Re: Add rows in table between start and enddate

maxgro — Fri, 06 Feb 2015 21:15:21 GMT

maybe adding a filter (bold)?

LOAD

rowno() as id,

diff,

start,

end,

endmonth,

connr,

[amount pm]

FROM File.xls

(biff, embedded labels, table is Sheet1$)

;

load *

where day(newdate)=1;

load id,

date(start + iterno()-1) as newdate

Resident t

While start + IterNo()-1 <= end;

Re: Add rows in table between start and enddate

giovanni16 — Fri, 06 Feb 2015 22:38:45 GMT

Hi Massimo,

I tried, not working.

What I need is: Each row is uniek. I need to multiply it by the number in the column DIFF (month difference) and create as many rows.

For each row 1 month... (it should be the first date of the month)

Thank you in advance..

Re: Add rows in table between start and enddate

maxgro — Sat, 07 Feb 2015 08:54:28 GMT

this?

top table is your source data (first record only)

bottom is the output: one record for every month between start / end fields (mar-2015....dic-2017)

Re: Add rows in table between start and enddate

giovanni16 — Sat, 07 Feb 2015 13:02:29 GMT

Massimo,

Yes. That is what I need to achieve. How?

Re: Add rows in table between start and enddate

Anonymous — Sat, 07 Feb 2015 13:40:36 GMT

Hi,

Please use below script:

Set DateFormat = 'DD/MM/YYYY';

Data:
LOAD * INLINE
[
Date
26/07/2011
29/09/2011
13/12/2011
];

LOAD Date,
Year(Date) as Year,
Month(Date) as Month,
Date(Date,'MMM/YYYY') as MonthYear,
Monthname(Date) as MonthName;
Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(Date) AS MinDate,
Max(Date) AS MaxDate
RESIDENT Data;

Regards

Neetha

Re: Add rows in table between start and enddate

Anonymous — Sat, 07 Feb 2015 13:48:39 GMT

You can modify to your requirement:

Load
Min(Start date) AS MinDate,
Max(end date) AS MaxDate
RESIDENT Data;

Re: Add rows in table between start and enddate

maxgro — Sat, 07 Feb 2015 14:18:24 GMT

PFA

Re: Add rows in table between start and enddate

giovanni16 — Sat, 07 Feb 2015 14:43:25 GMT

Neetha,

Thank you for your reply.

However, it is not working. Your script creates records for each day in the month. I need 1 record per month for each row in the table. Max rows is the difference in number of months.

For example, for the first record, the difference between the start an the end date is 34 months, I need than 34 records. (The amount is the monthly amount)

For the second row, the difference is 36 months, thus 36 records

For thethird row, the difference is 36 months, thus again 36 records an so on....

But each new record need to have a new column with the first day of the month.

See the post from Massimo. That is what I want to achieve.

Re: Add rows in table between start and enddate

Anonymous — Sat, 07 Feb 2015 14:43:34 GMT

Giovanni

The IntervalMatch() function could be what you need.

Have a look at this blog post IntervalMatch by Henric Cronström

Bill

Re: Add rows in table between start and enddate

Anonymous — Sat, 07 Feb 2015 15:04:26 GMT

So you want to generate dates(MMM/YYYY) and split dates monthly starting with day 1st of each as new column.

am i right?

Re: Add rows in table between start and enddate

rupaliqlik — Sat, 07 Feb 2015 15:05:25 GMT

Use IterNo function to capture dates in between start date and end date

Temp:

load * inline

[

StartDate,EndDate

01/03/2015,31/12/2017

];

TEMP1:

load

StartDate,

EndDate,

Date(StartDate+IterNo()-1) as Date

resident Temp

While (StartDate+IterNo()-1)<=Date(EndDate);

Re: Add rows in table between start and enddate

giovanni16 — Sat, 07 Feb 2015 15:36:19 GMT

Massimo,

It seems the solution was in the filter.

Thank you very much for your help. It works now.

Re: Add rows in table between start and enddate

giovanni16 — Sat, 07 Feb 2015 15:41:53 GMT

Neetha,

Yes that's correct.

Massimo found the solution.

It was the use of a filter in the creation of records.

where

day(month)=1;

Thanks for your help.