group by , newest non-null values

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

select newest non-null value.yxmc

Example.xlsx

Transformation

Apps

Accepted answers

JohnJPS

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!

SelectFirstNonNull.yxmc

SelectFirstNonNull.yxmd

jdunkerley79

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).

Select Newest NonNull With Grouping 2.yxmc

All comments

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703