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.
Solved! Go to Solution.
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?
maybe adding a filter (bold)?
rowno() as id,
(biff, embedded labels, table is Sheet1$)
date(start + iterno()-1) as newdate
While start + IterNo()-1 <= end;
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..
Please use below script:
Set DateFormat = 'DD/MM/YYYY';
LOAD * INLINE
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);
Min(Date) AS MinDate,
Max(Date) AS MaxDate