topic Re: How to fill missing date value with the previous date max value using talend in Talend Studio

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

krengan21 — Mon, 04 Dec 2017 14:01:50 GMT

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

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

Anonymous — Mon, 04 Dec 2017 23:54:33 GMT

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

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

Pantonio — Fri, 16 Sep 2022 10:59:59 GMT

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.

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

Anonymous — Fri, 16 Sep 2022 11:08:46 GMT

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

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

Pantonio — Fri, 16 Sep 2022 11:27:39 GMT

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.

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

Anonymous — Fri, 16 Sep 2022 11:46:51 GMT

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.

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

Pantonio — Fri, 16 Sep 2022 12:40:44 GMT

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

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

Anonymous — Fri, 16 Sep 2022 14:12:14 GMT

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.....

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

Pantonio — Fri, 16 Sep 2022 14:48:35 GMT

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!