date_from and date_to split to several records intervals

skaradub — Tue, 09 Oct 2018 15:49:45 GMT

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

Re: date_from and date_to split to several records intervals

andrey_krylov — Tue, 09 Oct 2018 17:42:49 GMT

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

topic date_from and date_to split to several records intervals in App Development

date_from and date_to split to several records intervals

Re: date_from and date_to split to several records intervals