How to populate before and after missing dates

[SabrinaO]

Hi,

could someone help me?

I have a file that runs from January 4th to January 29th, with working days only.

I need to generate a report that groups the information by Code, by Date column and sums the Value column.
Where I'm struggling is: I also need to generate holidays and weekends (the days generated must replicate the information from the previous days, except when rows generated are first, because there will be no value above to replicate).

I used the Summarize tool to get the minimum and maximum dates and generate the missing dates in between.

However, this way, I was not able to generate the dates before and after the ones I already have in the report.

Input example:

Date	Code	Value
2021-01-04	Y	1
2021-01-04	X	2
2021-01-04	X	3
2021-01-05	Y	1
2021-01-05	Y	1
2021-01-05	X	2
2021-01-06	Y	1
2021-01-06	X	2

Desired output:

Date	Code	Value
2021-01-01
2021-01-02
2021-01-03
2021-01-04	X	5
2021-01-05	X	2
2021-01-06	X	2
2021-01-07	X	2
2021-01-01
2021-01-02
2021-01-03
2021-01-04	Y	1
2021-01-05	Y	2
2021-01-06	Y	1
2021-01-07	Y	1

Is there a way to do this?

Thank you!

[JagdeeshN]

Hi @SabrinaO ,

Have you tried the MultiRow formula tool and the Gernerate Rows tool for this solution?

Best,

Jagdeesh

[SabrinaO]

Thanks for the quick answer @jagdeeshn! 

I used the summarize tool to get the min and max dates. Then with the generate rows i used the formula: DateTimeAdd([Date],1,"days") and  I was able to populate the dates missing (weekends and holidays) in between January 4th to 29th.

But, I couldn't generate the dates before January 4th and after January 29th, to get the whole month.
I also tried to include a new input with the dates and use a union tool to join. The issue is, when I try to group by code, the dates are still missing, because I have more than one code for the same date (Friday), so I need more than one Saturday and Sunday to populate all of them

[Luke_C · Accepted solution]

Hi @SabrinaO 

I think you could leverage the datetimetrim function for this, see below - after getting the max and min dates, you can just tweak each to go to the first and last day, respectively. 

datetimetrim([Min_Date],'firstofmonth')

datetimetrim([Max_Date],'lastofmonth')

[SabrinaO · Accepted solution]

Thank you so much! It worked