date_from and date_to split to several records intervals

[skaradub]

Hello there!

I've got task I couldn't find solvation within community and search engines.

As input I got user activity like this:

user	date_from	date_to
a	13.02.2018	22.02.2018
a	03.03.2018
b	17.01.2018	24.02.2018
b	22.08.2018

and I need for my model output like this:

user	date_from	date_to	month	days
a	13.02.2018	22.02.2018	02.2018	10
a	03.03.2018	31.03.2018	03.2018	19
a	01.04.2018	30.04.2018	04.2018	30
....
a	01.09.2018	30.09.2018	09.2018	30
b	17.01.2018	31.01.2018	01.2018	15
b	01.02.2018	24.02.2018	02.2018	24
b	..... same as in a

I can't make date my date by IterNo cause I have 40m records as input and need 2 years scope. so see if there is any elegant way of creating only nessesary records.

regards,

Yury

[andrey_krylov]

Hi Yury, I think you can get this output using chart expressions

date_to:    If(Len(Trim(date_to)) = 0, MonthEnd(date_from), date_to)

month:      Date(date_from, 'MM.YYYY')

days:        If(Len(Trim(date_to)) = 0, MonthEnd(date_from), date_to) - date_from

or in load script - the same exps