Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Autogenerate rows from start date to end date

Hello community,

I'm wondering if there is an existing specific function to do what I want to do:

I have in my database x rows of data with a start date and an end date.

I want as output to have 1 row per month between the start and the end date, and that for each row of input.

Ex : if row 1 has : start date = 2016/01/12 and end date = 2016/03/21, then I want to have one row for january, one row for february, one row for march. And this functionning for each input row.

All I can think of is using FOR...NEXT or FOR EACH, but this seems to take too long.

Thanks

I will explain the context in case there is some other way of doing it

5 Replies
Partner
Partner

Re: Autogenerate rows from start date to end date

Context:

each row of data as input is a study, with a start date and an end date.

The indicator I need is the number of studies running, during each month, or each year.

So a study starting on 2016/01/12 and ending on 2016/03/21 would count :

- for 1 on dimension Month = january

- for 1 on dimension Month = february

- for 1 on dimension Month = march

- for 3/12 on dimension Year = 2016


Therefore I was planning to duplicate the rows to have them at month level, and to just count the rows to calculate the indicator (and divide by 12 if at Year level)

Not applicable

Re: Autogenerate rows from start date to end date

try with "Crosstable"

Partner
Partner

Re: Autogenerate rows from start date to end date

Thank you  Khawar. I didn't know that one and it is very interesting. However I don't see how I could make use of it in my case, because I don't have a crosstable, and I cannot turn my data into a crosstable.


Indeed I can't predefine the number of month between the start date and the end date, so I wouldn't know how many columns of months I would need in my crosstable.


I'm looking at intervalmatch though, it looks like something I could use!

Re: Autogenerate rows from start date to end date

Hi,

one solution to just duplicate the source rows with different months could be:

QlikCommunity_Thread_243879_Pic1.JPG

LOAD RecNo() as ID, *,

    MonthName(StartDate,IterNo()-1) as Month

INLINE [

    StartDate, EndDate

    2016/01/12, 2016/03/21

    2016/02/10, 2016/05/26

    2016/03/01, 2016/04/05

    2016/01/12, 2016/03/21

]

While MonthName(StartDate,IterNo()-1)<=EndDate;

But your description sounds like linking your table with a master table using the intervalmatch prefix is the better idea.

hope this helps

regards

Marco

Partner
Partner

Re: Autogenerate rows from start date to end date

Thank you very much Marco, I will look into that, it looks great!