topic Autogenerate rows from start date to end date in QlikView

Autogenerate rows from start date to end date

emmanueld — Tue, 20 Dec 2016 10:39:46 GMT

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

Re: Autogenerate rows from start date to end date

emmanueld — Tue, 20 Dec 2016 10:49:27 GMT

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)

Re: Autogenerate rows from start date to end date

Tue, 20 Dec 2016 10:51:30 GMT

try with "Crosstable"

Re: Autogenerate rows from start date to end date

emmanueld — Tue, 20 Dec 2016 13:09:28 GMT

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

MarcoWedel — Wed, 21 Dec 2016 00:39:02 GMT

Hi,

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

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

Re: Autogenerate rows from start date to end date

emmanueld — Wed, 21 Dec 2016 08:53:14 GMT

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