Add extra rows where months are missing

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)

Dynamic Processing

Expression

Accepted answers

All comments

aatalai

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

binu_acs

@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

Missing_Fields_25032024.yxmd

angel0v89

@aatalai can you give me an example?

@binu_acs 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

TS example.yxmd

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

TS example v2 with randomsample.yxmd

binu_acs

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

angel0v89

Hi there,

I am still struggling to understand this TS Filter.

@binu_acs 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

Sample.xlsx

binu_acs

@angel0v89 one way of doing this

Generate_Missing_Records_27032024.yxzp

Chris-Mace

@angel0v89 here is my method for padding the data for each customer from any start date added to the text input tool. It dynamically finds the current month when you run the workflow so it will continue to add more rows as needed. I also use the Data Cleansing tool to replace any Null values with Zero. Screenshot 2024-04-22 091526.png

Padding Customer and Dates.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703