Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 24, 2020 8:16 AM
(186 views)

There is missing value in my data.

I'm trying to create script for new column 'Row Number' to get row number of lagged value of Col1(non-missing data).

I'm facing problem when missing data is encountered.

Below is my data with required row number outcome.

How to create script for this?

Row# | Col1 | Row Number(outcome) |

1 | 11 | |

2 | 18 | 1 |

3 | 34 | 2 |

4 | 3 | |

5 | 3 | |

6 | 3 | |

7 | 20 | 3 |

8 | 7 | |

9 | 7 | |

10 | 24 | 7 |

11 | 14 | 10 |

12 | 17 | 11 |

13 | 12 | |

14 | 12 | |

15 | 16 | 12 |

16 | 25 | 15 |

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I think this will do what you're looking for. Lag() without an argument will give the lag of the current column.

```
New Column( "r",
Formula(
If(
Row() == 1, .,
Is Missing( Lag( :Col1 ) ), Lag(),
Row() - 1
)
)
)
```

2 REPLIES 2

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I think this will do what you're looking for. Lag() without an argument will give the lag of the current column.

```
New Column( "r",
Formula(
If(
Row() == 1, .,
Is Missing( Lag( :Col1 ) ), Lag(),
Row() - 1
)
)
)
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to Get Row Number When There is Missing Data

It works.

Thank you.