# Find frist date with non zero value in a time series **URL:** \ **Category:** KNIME Analytics Platform\ **Tags:** time-series\ **Created:** [January 14, 2021, 9:00am UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936 "2021-01-14T09:00:41Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![goodvirus](https://forum-cdn.knime.com/user_avatar/forum.knime.com/goodvirus/32/35749_2.png) [@goodvirus](https://forum.knime.com/u/goodvirus)\ **Post date:** [January 14, 2021, 9:00am UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/1 "2021-01-14T09:00:41Z")
Hi fellow knime users, I have a problem that I just can’t wrap my head around. Given the following Time Series Data: Location Date Usage H1 01.01.2021 0 H1 02.01.2021 0 H1 03.01.2021 0 H1 04.01.2021 1 H1 05.01.2021 2 H2 01.01.2021 0 H2 02.01.2021 0 H2 03.01.2021 1 H2 04.01.2021 0 H2 05.01.2021 2 There are different Locations, all with the same start and end date, but some start with 0 and I want to find the first (not all, just the first) non zero value per location and want to remove the leading zero rows. So the transformed data would look like this: Location Date Usage H1 04.01.2021 1 H1 05.01.2021 2 H2 03.01.2021 1 H2 04.01.2021 0 H2 05.01.2021 2 Extra points for a solution where I can define the window of how many leading values should be removed. The only solution I can think of is a dopple loop, but that not a very elegant solution. Thanks very much in advanced ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [January 14, 2021, 10:46am UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/2 "2021-01-14T10:46:44Z")
Hello @goodvirus, here is one approach. Use [GroupBy](https://kni.me/n/5stmXk6zY_ORA4bC) node and group by Location while concatinating Usage without delimiter. Then use [String To Number](https://kni.me/n/gXR6yic77CM35Zfx) node which will drop all leading zeroes. For extra points you can use [String Manipulation](https://kni.me/n/eOn8ibZnxIkY7XhT) node instead of String to Number node together with regex in order to determine window of how many leading values should be removed. (here window is two - `regexReplace($column$, "^0{0,2}(.*)", "$1")`). The tricky part is now to get back to original data format with dates matching usage. One way is to calculate difference between string lengths (one produced by GroupBy node and one produced by String Manipulation node) which will tell you how many rows to filter for each Location. And for this task you go back to Loops 🙂 Give it a try and if any questions feel free to ask. Br, Ivan ---
**Author:** ![goodvirus](https://forum-cdn.knime.com/user_avatar/forum.knime.com/goodvirus/32/35749_2.png) [@goodvirus](https://forum.knime.com/u/goodvirus)\ **Post date:** [January 14, 2021, 2:27pm UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/3 "2021-01-14T14:27:36Z")
Hi @ipazin, thank you very much for the idea. I implemented it and it works (quit a big workflow for a small problem). Probably that can be done much quicker and leaner, but who cares? (I do, if you have a better solution please post it). If someone has the same probleme, see my workflow attached. Thanks, Paul[remove\_start\_values.knwf](https://forum.knime.com/uploads/short-url/2Xy0QJa2m4L5hc2MtXC8Ik4tDyQ.knwf) (39.1 KB) ---
**Author:** ![goodvirus](https://forum-cdn.knime.com/user_avatar/forum.knime.com/goodvirus/32/35749_2.png) [@goodvirus](https://forum.knime.com/u/goodvirus)\ **Post date:** [January 14, 2021, 3:11pm UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/4 "2021-01-14T15:11:57Z")
Hi again, now I applied the solution to my big workflow (with a couple of millions of entries) and now the problem ist, that the values go so big that not even long numbers can hold them… Any suggestions? ---
**Author:** ![goodvirus](https://forum-cdn.knime.com/user_avatar/forum.knime.com/goodvirus/32/35749_2.png) [@goodvirus](https://forum.knime.com/u/goodvirus)\ **Post date:** [January 14, 2021, 4:01pm UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/5 "2021-01-14T16:01:20Z")
I think the knime version is to difficult and so I used a python note within a group loop (that could also be done in python). The python code: `output_table_1 = output_table_1.loc[output_table_1['auslastung'].ne(0).idxmax():]` ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [January 15, 2021, 9:54am UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/6 "2021-01-15T09:54:41Z")
Hello @goodvirus, glad you found a way. Scripting is probably better in this scenario. And you are right, when values are too big idea with conversion won’t work. However regex from String Manipulation will still do the trick. Check modified workflow: [remove\_start\_values\_ipazin.knwf](https://forum.knime.com/uploads/short-url/uWIdzSXNLlY3oku7iu6mYyzUbm4.knwf) (51.5 KB) Br, Ivan ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [January 22, 2021, 9:54am UTC](https://forum.knime.com/t/find-frist-date-with-non-zero-value-in-a-time-series/29936/7 "2021-01-22T09:54:43Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.