Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
skaradub
Contributor
Contributor

date_from and date_to split to several records intervals

Hello there!

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

As input I got user activity like this:

userdate_fromdate_to
a13.02.201822.02.2018
a03.03.2018
b17.01.201824.02.2018
b22.08.2018

and I need for my model output like this:

userdate_fromdate_tomonthdays
a13.02.201822.02.201802.201810
a03.03.201831.03.201803.201819
a01.04.201830.04.201804.201830
....
a01.09.201830.09.201809.201830
b17.01.201831.01.201801.201815
b01.02.201824.02.201802.201824
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

1 Reply
andrey_krylov
Specialist
Specialist

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