dynamically select ONLY group-by columns using macro

[DannisMen]

hi, 

just wonder if there is a way to only select group-by columns without using transpose and crosstab. 

Note: the numeric fields are all set and static regardless of group-by field. 

thank you!

[seinchyiwoo]

Not too sure I get what you are trying to achieve.

Have you tried groupby in Summarize tool?

Best,

Seinchyi

[DannisMen]

Sorry for not getting this clear. i actually want a macro to do it.

[mbarone]

The only way to do that (that I know of) is to actually re-write the Summarize tool by updating the XML of the tool itself.  In the Action Tool, using either the update value default or update value with formula won't do it because those options only update one line or section of code.

You'd have to use the update raw XML option in the Action tool.  I'll try to put together a sample module that you can use as a model.  It's not the easiest or most straight forward task.

[mbarone · Accepted solution]

Here's a sample of what you'd have to do.

Say I have a table connected to a Summarize tool:

If you look at the XML of the Summarize tool (by clicking on the XML icon on the config pane) you see this:

What you want to do is build your own XML to overwrite this.  So if I want just Address and Zip to be grouped on, the XML would have to look like this:

<Configuration>
  <SummarizeFields>
     <SummarizeField field="Address" action="GroupBy" rename="Address" />
     <SummarizeField field="ZipCode" action="GroupBy" rename="ZipCode" />
  </SummarizeFields>
</Configuration>

You would connect an action tool to the Summarize tool and configure it like this:

<Configuration>
  <SummarizeFields>
     <SummarizeField field="Address" action="GroupBy" rename="Address" />
     <SummarizeField field="ZipCode" action="GroupBy" rename="ZipCode" />
  </SummarizeFields>
</Configuration>

The tricky part is the selections from the list box come in like this:
Name=False,Address=True,ZipCode=True

So you have to build the XML within the action tool so that it comes out like the blue above.  I'm not an XML expert or RegEx expert, but I think you'd have to make use of those functions.

But, if you can get that XML into the action tool:

Then the summarize tool will be configured as such when it runs.

Hope this helps!

[DannisMen]

thanks for your help. i think that should work once i figure out the XML.

but i have a problem here on XML. can you plz take a look at it?

[mbarone]

Sure - you need single quotes at the beginning and end of your expression in the action tool (single quotes because double quotes are actually used as part of the XML). 

[DannisMen]

that helps!

another question here: how can i give a list tool so that i can update the XML with a new column that i want group by?

[mbarone]

Well, that's the hard part that might require some advanced RegEx functions.

With the formula tool, what you're doing is inserting the incoming data connection (here, [#1]).

For a simple example, if you have a hard coded formula in, say, a filter tool that says:

[StartDate]="2020-09-01"

And you want to update the date to whatever [#1] is, then you'd do this in the action tool:

'[StartDate]="'+[#1]+'"

The issue you'll have with your use case here is how [#1] comes in from the list box.  Say the list box has options for the user to select Field1, Field2, Feild3, and/or Field4.  And say they select just Field1 & Field2.

The data will come into the action tool as:

Field1=True,Field2=True,Field3=False,Field4=False

So it'll take some parsing to get it in the format you need.

[DannisMen]

hmm. i never thought it's gonna be that complicated. thank you so much for your time! i probably will still use transpose and crosstab..