I have a requirement wherein I need to substitute the NULL rows in Spotfire with the next valid value after it. This substitution should be done per "State-City" combination. I tried using FirstValidAfter() function in a calculated column but looks like it cannot be used in combination with OVER function to group the data. If there is not valid value available for substitution within the group then the cell can be kept blank.
The data will always be sorted according to the "Number" column.
| Number | State | City | Using FirstValidAfter(City) | Expected Output |
|---|---|---|---|---|
| 1 | Maharashtra | NULL | Mumbai | Mumbai |
| 2 | Maharashtra | Mumbai | Mumbai | Mumbai |
| 3 | Maharashtra | NULL | Jaipur | NULL |
| 4 | Maharashtra | NULL | Jaipur | NULL |
| 5 | Rajasthan | NULL | Jaipur | Jaipur |
| 6 | Rajasthan | NULL | Jaipur | Jaipur |
| 7 | Rajasthan | Jaipur | Jaipur | Jaipur |
| 8 | Karnataka | Bangalore | Bangalore | Bangalore |
| 9 | Karnataka | NULL | NULL | NULL |
Can something like this be achieved using calculated columns or python script? If yes, then how? Appreciate any help!
I tried using FirstValidAfter() function in a calculated column but looks like it cannot be used in combination with OVER function to group the data. The table shows the output after using FirstValidAfter().
This should work
First([CITY]) OVER (Intersect([State],AllNext([Number])))