generate the row based on condition

[alt_tush]

Hi All,

I have below data where my Report_Date data type is Date.

UserID	CostCenter	Report_Date
100	A100	15-01-2021
100	B100	19-01-2021
100	C100	31-01-2021
200	A200	25-01-2021
200	B200	31-01-2021
300	A300	01-02-2021
300	B300	11-02-2021
300	C300	21-02-2021
300	D300	26-02-2021

My expected output is to generate the multiple rows by incrementing the report by 1 on group level of user id and cost center.

If in my first row Userid is 100 , cost center is A100 and report date 15-01-2021 then my new row generation would be 16-01-2021, 17-01-2021 and so on till 18-01-2021 i.e. next cost center and report date change for same user id. In above case report date is changed on 19-01-2021. So next incremental entry should be based on new cost center i.e. B100 and Report Date 19-01-2021 till next report date.

It means my output would be as below

UserID	CostCenter	Report_Date
100	A100	15-01-2021
100	A100	16-01-2021
100	A100	17-01-2021
100	A100	18-01-2021
100	B100	19-01-2021
100	B100	20-01-2021
100	B100	21-01-2021
100	B100	22-01-2021
100	B100	23-01-2021
100	B100	24-01-2021
100	B100	25-01-2021
100	B100	26-01-2021
100	B100	27-01-2021
100	B100	28-01-2021
100	B100	29-01-2021
100	B100	30-01-2021
100	C100	31-01-2021
200	A200	25-01-2021
200	A200	26-01-2021
200	A200	27-01-2021
200	A200	28-01-2021
200	A200	29-01-2021
200	A200	30-01-2021
200	B200	31-01-2021
300	A300	01-02-2021
300	A300	02-02-2021
300	A300	03-02-2021
300	A300	04-02-2021
300	A300	05-02-2021
300	A300	06-02-2021
300	A300	07-02-2021
300	A300	08-02-2021
300	A300	09-02-2021
300	A300	10-02-2021
300	B300	11-02-2021
300	B300	12-02-2021
300	B300	13-02-2021
300	B300	14-02-2021
300	B300	15-02-2021
300	B300	16-02-2021
300	B300	17-02-2021
300	B300	18-02-2021
300	B300	19-02-2021
300	B300	20-02-2021
300	C300	21-02-2021
300	C300	22-02-2021
300	C300	23-02-2021
300	C300	24-02-2021
300	C300	25-02-2021
300	D300	26-02-2021

Thank you in advance.

[Christina_H · Accepted solution]

Here you go

[Jonathan-Sherman]

Hi @alt_tush,

I've taken a slightly different method to @Christina_H to avoid the union:

(1) Convert date into correct date data type

(2) Calculate the end date to generate records to

(3) If we've got no end date, set end date as report date

(4) Generate rows between the dates

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[alt_tush]

Hi Cristina H,

Thank you so much for your help. Its resolved my blocking issue. That exactly i wanted, 

Thanks again 🙂