How to add days to an existing date sequence with missing days?

[yswaaij001]

Hi everybody,

I am trying to build a tax compliance work flow that automatically retrieves fx rates from a website, which the work flow can subsequently append to existing invoices that were issued in another currency (invoices in EUR while tax reporting requires a local currency; e.g., SEK). This website does however not populate daily fx rates (see the output below in red color) as this specific fx rate does not always change on a daily basis. The missing dates cause errors when my workflow tries to append the fx rate for a missing day (e.g., an invoice dated on 2024-01-20). How can I add the dates 2024-01-20 and 2024-01-21 (plus other missing dates) to my overview below while allocating the oldest fx rate to those additional dates (e.g., 11.38 SEK in the example below)? I have attached the relevant part of my workflow as attachment to this question.

Date	f/x rate (SEK)
2024-01-23	11.347
2024-01-22	11.3545
2024-01-19	11.38
2024-01-18	11.364
2024-01-17	11.3925
2024-01-16	11.3215
2024-01-15	11.278

Don't have much experience with Alteryx yet, so I am hoping that someone can provide support on this query.

thx in advance.
 
Yvo van Swaaij
 
 
 
 

[DataNath · Accepted solution]

Hey @yswaaij001, how does this look? We can use the Generate Rows tool to do exactly what it says on the tin - fill missing values in this case. To explain the steps a little more I have:

1) Sorted by [Date] Ascending

2) Used a Multi-Row Formula to place the next day/row value next to the current

3) In the Generate Rows tool, used a condition where - if the day difference between this next [Date] and the one on the current line is more than 1 - we generate extra rows until that's no longer true

4) Used the Select tool to make this new, full date field the new [Date] column and removed the other temporary fields used to get to the final product

Hope this helps!

[aatalai]

 the TS filler tool can also provide you a list of dates between two dates, daily and excluding weekdays, that might be simpler

[Hsolmaz]

I can share another macro with you that downloads the fx rates between two dates from Yahoo Finance without any missing data. That might help. Reference topic

[yswaaij001]

thx very much!

this is highly appreciated and enables me to proceed with this workflow.