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