Frequency Tool - Need to "Group By"

[aesham]

Hello,

I need to create a price frequency table by market and by brand.  The Frequency tool produces terrific output but it seems to only be able to read the entire dataset; there is no "group by" function.  I checked with online support and was told to request a "group by" function in the developers forum or to create a macro.   I was referred to the online training sessions on macros - but they don't seem to address my issue.  My need is immediate and I have no idea how to produce a macro to handle this.  My hope is to incorporate the Frequency tool to take advantage of the 3 built-in outputs.

My dataset and prelinary workflow is attached.  I want to create a frequency table by "market num" and "brand num" using the price data in the field "750$"

Any help in creating the macro or other solution is greatly appreciated.  Thank you.

Andrew

[BenMoss]

Hi,

What you need in this situation is an iterative macro.

What we can do with this is essentially treat each of our data chunks seperately.

An iterative macro can process either a single row, or group of data at the same time. By creating a unique ID for each group we can then create a filter which is defined as 'does the iteration number equal the unique ID', if it does we manipulate that part of the data. It then stores that data, and runs a 2nd, 3rd, 4th iteration untill all records have been processed or a set number of iterations has been reached.

Would it be possible to share the data with your workflow by creating a  packaged workbook 'options > export workflow'. 

Otherwise I will build out an example for you with some sample data.

Ben

[BenMoss]

Here I have created an example of how you can use an iterative macro which segreagates 4 groups in my dataset creates a frequency report for each then outputs them in a single stream.

You should be able to adapt this for your use case. 

[aesham]

Hello Ben,

Thanks for the help.  I inserted my dataset into your workflow and grouped by "market num" and "brand num".  However, when I look at the frequency table tool within the macro it contains the fields in your dataset not mine; I am unsure how to update it

Attached is the packaged workbook.   If you can help me adapt your flow I would greatly appreciate it.

Thank you,

Andrew

[BenMoss]

Hi Andrew,

When building using a macro input you have to put in a placeholder dataset that will essentially contain the structure that your data will come in as.

Here you can point it at your file, but this should be as the file looks as it is coming into the macro, so with the 'uniquegroupID' field that we created. I have done this for you and attached the workflow to this message.

As a further point, I have developed the iterative macro so it now outputs the report and interactive report streams.

Hope this helps.

Ben

[aesham]

Hi Ben,

Thanks for all your help.  I am really learning alot.

Unfortunately, when I run the workflow I am not getting the desired output.  What I am looking for is something like this:

Any suggestions would be appreciated.  Thanks, Andrew

[BenMoss]

Can I ask if those are true frequencies that you actually expect or just a made up sample?

It is important to know that you can only create a frequency table from string type data. So if you wish to be able to create a frequency table of the values within column 750$ then you will need to convert this to a string field.

You will then have to output this as a template, and alter the macro accordingly.

Your output should then be as desired.

The onlyr reason I question whether the frequencies given in your example table are true is because I have made the above ammendments and appear to get diferent frequencies.

[aesham]

Ben, hi.

Thanks for hanging in there with me.  Yes, the data I presented was dummy data, for illustration only.  I am looking to produce the exact output that you show in your last post.  I will make the string change to 750$ and see what I get.  Could you also attach the workbook that produced the final table?  I would be helpful as a guide in case I get stuck again.

Thank you so much for your help!  I appreciate the solution workbook and all the tips and guidance you've provided along the way.

Andrew

[BenMoss · Accepted solution]

No Problem at all!

I have attached the latest version of the workflow here.

If you are only interested in the frequencies for this one column then I would consider using a simple sumamrise tool, this would be much more efficient (iterative macro's can be quite slow). And you could infact develop it for multiple columns by repeating the process with another field then using a union. I have also included an example of how you would do this.

Please continue to reach out if you need more help with this!

Ben

[aesham]

Ben,

Thank you very much for the solution workbook.  Also, I never thought of using Summarize as a means of counting frequencies.  You've been a super great help.  I truly appreciate it.

Best,

Andrew