 # Filling in gaps in a series in a list

I have a list of sparse values..i.e. some are missing. And for those missing values I want to populate them with the last known value. For example, from the first list below, I want to produce the second list.

`````` ['25','27','','26','','','','28']
...
['25','27','27','26','26','26','26','28']
``````

I think it requires an accumulator, like in reduce().

Hello @mojo2go It took me a while but I found a way:

``````WITH ['25','27','','26','','','','28'] AS l
RETURN [x in range (0, size(l)-1) |
CASE WHEN l[x] = '' THEN
[t in [y in range(0, x-1) |
CASE WHEN l[y] <> '' THEN l[y] END] WHERE t IS NOT NULL][-1]
ELSE l[x] END]
``````

Regards,
Cobra

Nice work Cobra!
It works perfectly but can you help me with how it works?

I can see that the top-level list comprehnsion walks methodically through the original list, and for each element it just writes it back out to a new list if the element is not an empty-string. So far so good!
But if that top-level CASE does detect an empty string, then you run a new list comprehension within the first, just for determining what should be the value for that element.
There you create a new range of all indexes of elements already covered (from zero up-to-but-not-including the current index). Then you walk that list starting from zero, skipping over empty-strings, continually overwriting the value of t. So t keeps changing with every valid value in the list until it has checked all previous values. The last valid value that t holds will be the latest valid value available before our empty-string.

If that is correct, what is the construct you are using when you select that last good value?
I'm confused at "[t in [y i". Are there 3 list comprehensions here?...or two?

1 Like

Yes, you are right There are three comprehension lists, to get the good value, I just select the last value (`[-1]`) of this result list:

• This part returns the list of values before `l[x]` but it contains some `null` values
``````[y in range(0, x-1) | CASE WHEN l[y] <> '' THEN l[y] END]
``````
• That's why I clean the result list to remove null values from it and select the last value in the list:
``````[t in \$previous_list WHERE t IS NOT NULL ][-1]
``````
• So in the end:
``````RETURN [x in range (0, size(l)-1) | CASE WHEN l[x] = '' THEN \$last_value_of_previous_list ELSE l[x] END]
``````

Hope it helps you to understand, it was very tricky to code Now I understand the [-1], and the NULL.

Also I didn't realize that you don't need to include a pipe (|) if you're not going to transform the elements.

Thanks so much for the explanation. This was a great learning session.

1 Like

Hi Cobra,
This is just for your interest (and part of my learning process). I made a variation on your solution that keeps your top level list comprehension but replaces the inner two list comprehensions with a reduce function. This reduce doesn't accumulate anything it just keeps passing forward the last good value.

``````WITH ['25','27','','26','','','','28'] AS l
RETURN [x IN range (0, size(l)-1) |
CASE WHEN l[x] = '' THEN
reduce(lastgoodx='', y IN range(0,x-1) | CASE WHEN l[y] <> '' THEN l[y] ELSE lastgoodx END)
ELSE l[x] END]
``````

Joe

1 Like

Like this, we are both learning Nice idea!

1 Like