In Summarize, how to select last non-null value in a field?

[stanleychen]

I have a dataset like this, i would like to summarize by grouping transaction ID and selecting the last non-null value, if any (if no non-null value, i would like to have null shows in the output). The intended output is as shown below in the second table. Please kindly advise what can i do? Thanks!!

Transaction ID	Payment plan
1	A
1
1	B
1
2	A
2
2
3
3	B
3
4
4

Transaction no.	Payment plan
1	B
2	A
3	B
4

[CRowlatt · Accepted solution]

Hi Stanleychen,

For what you need is it better to use a filter to get rid of the nulls, then summarise (group by ID and last payment plan), then join back to your original data set and union to find the unmatched (null) transaction IDs?

Chris

EDIT: I've attached a workflow, it's a bit scrappy, but seems to work as you want?

[afv2688 · Accepted solution]

Hi @stanleychen, for this you can use:

1. Summarize tool first:
   
   • Group by Transaction ID
   • Concatenate Payment plan
2. Formula tool after
   • Select the concatenated field and add the formula < Right([Concat_Payment ID], 1) >

cheers!