group by , newest non-null values

[DavidS1]

Hi everybody

I created a macro that can perform a group by operation on an ID and seelct the newest non-null values.
since I use this macro multiple times with different tables I would like to be able to specify multiple group by attributes:  name_1 group by, name_2 group by, name_3 grou by, ...
So I would like to use my macro in the same way as a group by statment there I can allocate multiple attributes to group by and multiple use to Newest Non NULL.

Can sombody give me some pointers how I can do this with the interface tools? I added an Exel File with two sheets (input and output data).

Cheers,
David

[JohnJPS]

He @DavidS1,

I ran the macro against some test data (same as your template data, but with a fiew extra fields), and it correctly gave me the first non-null value for every additional field in my data.  This is due to the "Dynamic or Unkown Fields" check boxes being checked in the initial "Transpose" tool in your macro..

With that checked, it is assumed that it will process ALL fields, and there is no neeed to even specify the "test" field at all (e.g. you can remove it from the template data.)

If you uncheck that field in the macro, then the macro already allows you to pick the "Test" field, and it will then output only the that field; (although for me it still named it the output "Test").  Anywa - hope that helps!

[DavidS1]

Hi @JohnJPS

Yes Because of the field " Dynamic or Unknown Fields" my macro selects the newest non-null value for all columns (excluding the one mapped to id.)
But what if I want to have multiple ID like in my example file? Sometime i have 1,2 or more fields which would be identifieres.

[jdunkerley79]

Try the attached.

It uses an action tool to update a sample tool's grouping.

The grouping needs to be set by fiddling with the XML, the action tool does this by changing the output of the listbox into the expected XML formal.

[DavidS1]

Hi

Thanks for you macro, finally i see how the list box can be used, I couldn't figure that out...
Can you explain why you update the Select operator? I don't relly get that...
Unfortunattly the output is not correct: I need the newest non-null vlaue for each group and colum.
At the moment I only get the newest row by group. (see output-sheet in exel file: I want to achive this output)

Can I use the Update operator on the summerize and transpose operator? For example group by all names comming from the update operator?

Thanks and cheers,
David

[jdunkerley79]

Hi David,

I chose to use Sample tool to pick the single newest row for the record. This was just as then only needed to rewrite the XML for one tool.

Yes you can do across the various tools but a little more complex. Rewriting each XML is a bespoke operation. I did a blog exploring this a little while ago: https://jdunkerley.co.uk/2016/06/10/creating-user-controlled-groupings-in-a-weighted-median-alteryx-...

I will stick an example doing what you want together and send over

James

[JohnJPS · Accepted solution]

I sat down last night and got the XML mods together to allow separate Grouping, Ordering, and Data List Boxes, to allow multiple sort conditions, multiple ID fields, and multiple output fields.  For that kind of variety, the original solution that @DavidS1 provided (using Transpose and CrossTab) seemed easier to work with in order to produce good results.

I used "Generate Custom List" for the List Box tools and supplied pseudo-xml-table-looking tags for the list; then used Replace in the action tool Formulae, to massage the pseudo-xml into the desired XML (using "Replace raw XML with Formula" and "Update Outter XML") at each stop.  While there may be other ways to go about it, I just looked at the Macro project (XML) in a text editor in order to figure out how to write each Formula to generate the correct XML.  Hope that helps!

[jdunkerley79 · Accepted solution]

Nice one @JohnJPS

I always like poking the Inner Xml. If you turn a setting on inside Alteryx you can see the Xml in properties panel.

I have attached my version (based off yours). I use basically the same update formula 5 times.

I have a preference for Select over summarise (no real reason).

[JohnJPS]

That's great @jdunkerley79 -- I couldn't grok how to make it work with sampling. Your actions are simpler as well; nicely done.

I found the Display XML setting too...

Very handy -- needless to say, it is now checked for me!

[DavidS1]

Thank guys!

Awesome solutions :-)