Add extra rows where months are missing

[angel0v89]

Hi all,

I have been trying to resolve something for a few days now and still can't figure it out. I have a workflow that generates a lot of different financial data aggregated like:

My mission is to generate a 0 rows for the missing months for each client between Jan-16 to Mar-24. (Same as the highlighted rows)

[aatalai]

@angel0v89 take a look at the TS filler tool that should be able to help very easily

[binuacs]

@angel0v89 use the generate row tool and generate all the date till 2024-March and join the date with your input file then union the J-anchor recrords and the L anchor records . Sample attached

[angel0v89]

@aatalai can you give me an example? 

@binuacs I have tried this solution but nothing happens. If it is just a Date and value then yes it will but I do have a lot of other columns. So I would like them to be populated with the same values as the previous row (if the previous row exists). If it doesn't then the row should be the same as the last row.

For example above, the first 2 rows should be the same as the 3 row but with 0 amount.

For the missing rows, it should be the same as the previous row.

For the ones at the end should be from the previous one.

Hope this make sense?

[aatalai]

@angel0v89 sure see attached the true outputs are the ones that were missing. Let me know if this helps

[angel0v89]

Thanks @aatalai . I don't fully understand what this does. It seems that the interval is every 5 rows but in my dataset, it is not necessary to add rows for every 5th row?

[aatalai]

@angel0v89 the first 2 tools (generate rows and sample tool) is to generate a list of dates, as  a dummy input wasn't provided the the TS filler tool tells you whether or not a date is in the order. The intervals are just for an example and would work regardless; here it is with the random sample tool. Let me know if you have any further questions 

[binuacs]

@angel0v89 @Can you upload some sample data and expected output ?

[angel0v89]

Hi there, 

I am still struggling to understand this TS Filter. 

@binuacs  please find the Sample below. 

So the data I have is this:

ORIGINAL

Then I expect to populate it with the highlighted rows all the way back to 2016

I hope this make sense 

[binuacs]

@angel0v89 one way of doing this