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

Question

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 IDPayment plan1A1 1B1 2A2 2 3 3B3 4 4

Transaction no.Payment plan1B2A3B4

CRowlatt · Accepted Answer

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 Answer

Hi @stanleychen, for this you can use:

* Summarize tool first:* Group by Transaction ID
* Concatenate Payment plan

* Formula tool after* Select the concatenated field and add the formula < Right([Concat_Payment ID], 1) >

cheers!