Need to add weekend dates for multiple accounts and keep Friday's data

[ELPC]

Hi all, 

I saw a post about adding weekend dates that used a cool TS Filler tool but I have a slightly different variation because there are multiple accounts in my data instead of just one set.  How would I be able to generate the Sat and Sun dates keeping the same values (Account and Amount) of the previous Friday's data?  Thanks.  

Date                Account                 Amount

2020-07-01     Bank of America   100

2020-07-01     Wells Fargo           200

2020-07-02     Bank of America    127

2020-07-02     Wells Fargo           228

2020-07-03     Bank of America    156

2020-07-03     Wells Fargo            183

2020-07-06     Bank of America     278

2020-07-06     Wells Fargo            123

2020-07-07     Bank of America     286

2020-07-07     Wells Fargo            178

2020-07-08     Bank of America     212

2020-07-08     Wells Fargo            186

2020-07-09     Bank of America     265

2020-07-09     Wells Fargo            175

2020-07-10     Bank of America     279

2020-07-10     Wells Fargo            132

2020-07-13     Bank of America     246

2020-07-13     Wells Fargo            179

[AngelosPachis · Accepted solution]

Hi @ELPC ,

I think the attached workflow will do the trick! Although I have tested only in 2 accounts, it should work for more than 2 as well.

Essentially you are grouping on the date field and generate all the missing dates once. Then, you just have to join back to the main stream, and the new dates will fall out of the L output anchor.

If you group the different account names and use an append fields to generate each combination of account/missing date, you will get all missing rows.

The you only have to union back and use a multi-row formula tool to populate the null weekend values with the Friday values.

Let me know if that worked for you or if you have any questions.

Regards

Angelos

[pedrodrfaria]

Hi @ELPC 

I attached below a workflow that created the missing dates and adds the value from the previous day. This will calculate how many accounts and apply that logic to all the accounts. So you if you 2 or 2000, if you properly allocate everything and create each additional field for the missing days and will get the correct amount from the previous actual day.

I personally prefer the create rows logic because it gives me more flexibility. But this is also for educational purposes to show you different ways to do this.

Pedro.