Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
giovanni16
New Contributor

Add rows in table between start and enddate

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Add rows in table between start and enddate

15 Replies
MVP
MVP

Re: Add rows in table between start and enddate

giovanni16
New Contributor

Re: Add rows in table between start and enddate

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.

MVP
MVP

Re: Add rows in table between start and enddate

maybe adding a filter (bold)?

t:

LOAD

  rowno() as id,

  diff,

     start,

     end,

     endmonth,

     connr,

     [amount pm]

FROM File.xls

(biff, embedded labels, table is Sheet1$)

;

u:

load   *

where day(newdate)=1;              

load id,

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

Resident t

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

Highlighted
giovanni16
New Contributor

Re: Add rows in table between start and enddate

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..

MVP
MVP

Re: Add rows in table between start and enddate

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)

1.jpg

giovanni16
New Contributor

Re: Add rows in table between start and enddate

Massimo,

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

neetha_p
Honored Contributor

Re: Add rows in table between start and enddate

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

neetha_p
Honored Contributor

Re: Add rows in table between start and enddate

You can modify to your requirement:

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

MVP
MVP

Re: Add rows in table between start and enddate