How to fill missing date value with the previous date max value using talend

[krengan21]

How to populate missing dates with the value of previous date say for example

25-11-2017 1000

27-11-2017 2000

the missing date is 26-11-2017 how to fill it up with 25-11-2017 value using talend

[Anonymous]

I have written an example of how to do this using Talend 6.4. It is attached here. You will need to modify it to fully accommodate your requirements. You will need to modify it to (for example) handle multiple customers. However, the complicated work has been done. It uses row multiplication to create rows for the gaps in the days. Have a play and see how it goes.

FillDayGaps.zip

[Pantonio]

Hello @Richard Hall​ . Thanks for your help here. I wonder how I could populate zeros based on the earlier value so it would look like that:

08-01-2017|12.34

09-01-2017|12.34

10-01-2017|12.34

11-01-2017|12.34

12-01-2017|12.34

13-01-2017|12.34

14-01-2017|12.34

15-01-2017|234.7

16-01-2017|234.7

17-01-2017|234.7

18-01-2017|234.7

19-01-2017|234.7

20-01-2017|234.7

21-01-2017|23.8

I struggle with that one and I'd appreciate your tips.

[Anonymous]

I'm sorry, I don't understand what it is you are asking here.

[Pantonio]

Hello,

Output from your sample job attached gives:

08-01-2017|12.34

09-01-2017|0.0

10-01-2017|0.0

11-01-2017|0.0

12-01-2017|0.0

13-01-2017|0.0

14-01-2017|0.0

15-01-2017|234.7

16-01-2017|0.0

17-01-2017|0.0

18-01-2017|0.0

19-01-2017|0.0

20-01-2017|0.0

21-01-2017|23.8

I wonder how to modify the job to get mileage value in each row instead of zeros.

So let's say I have a data snapshot taken once per week but I want to have a table with a row for each day. Based on the earlier snapshot, I'd like to have values for filled days. The desired output is in my previous comment.

[Anonymous]

Oh I see. Can you share a screenshot of your job to give me an idea of what your version looks like? I am afraid I do not have a version of 6 running on my machine and it will take me a while to update the version I attached. It would also be better to see what changes you have made.

[Pantonio]

The attached copy of your job works fine in the Talend 8. I tried to modify tJava_flex but only what I get is value populated but from the bottom to top not as I want from the top to bottom:

08-01-2017|12.34

09-01-2017|234.7

10-01-2017|234.7

11-01-2017|234.7

12-01-2017|234.7

13-01-2017|234.7

14-01-2017|234.7

15-01-2017|234.7

16-01-2017|23.8

17-01-2017|23.8

18-01-2017|23.8

19-01-2017|23.8

20-01-2017|23.8

21-01-2017|23.8

[Anonymous]

OK, the issue was caused by the order in which the data was being processed and the algorithm used needed some tweaking to accommodate the change in order.Not significant changes. What I have done is add a tSortRow at the beginning to ensure the order coming in and a modification to the tMap code. You can see it here.....

[Pantonio]

Thank you so much @Richard Hall​ for your time to look into this! Now it works perfectly as I need. Have a great rest of the day!