Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Highlighted
Meteoroid

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 plan
1A
1 
1B
1 
2A
2 
2 
3 
3B
3 
4 
4 

 

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

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?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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!

 

Labels