Loop Filler tool for 27 different Bank Accounts in same data set

Question

I have data of 27 different Bank accounts I want to forecast but i am trying to use filler tool to make it consistent first, however i can't use it at the same time on all bank accounts, it will be really messy to have a filler tool for each bank account and then union them. Is there any way I can generate rows where I want in between data set or use filler tool in loop that changes with bank account.is it possible to use iterative macro to do that.

The data of transactions is weekly but sometimes some transactions are missing from data, so I was originally thinking of using filler tool to fill those dates to forecast.

DataNath · Accepted Answer

@akaur58 how does something like this look? As a batch macro, it pulls each account in one at a time, matches it to a generated range of weekly dates and then spits them all out back into the same data set:

Before:

After:

I wasn't sure what you wanted to do with missing values in terms of imputation etc so have just left them but this will hopefully set you up nicely to start with.

In terms of the generated range, this is hardcoded within the batch macro, which you can go into and edit by right clicking > open:

In the Generate Rows tool, you can see I'm just making a range between January 1st and January 29th for my example, but this can be anything you want, it's just creating a weekly date hence the +7 day expression at the bottom. Once you've edited this, just hit save on the macro and this will be reflected in the actual workflow when you flick back to it:

Hope this helps!

danilang · Accepted Answer

Hi @akaur58

Here's a dynamic non-macro way to fill in the missing rows

It builds a list of unique dates between the min and max in your dataset and cross joins this to the list of unique accounts.  Join this to your input data, union the missing rows and sort.

Dan