Frequency Tool - Need to "Group By"

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

Price Freq Sample.yxmd

Macros

Preparation

Accepted answers

BenMoss

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

Summarise Version.yxzp

Price Freq Example Latest.yxzp

BenMoss

This should be possible yes.

It is a bit more difficult with sheet names than it would be to completely seperate workbooks.

For completely differnet files you would simply set your output mode to 'choose a specific output file' then in the output file save your file, you can then use the 'group data into seperate records' and select the market name tab, this would append the market name to the filename, so essentially would would end up with something like..

FilenameMarket1.xlsx, FilenameMarket2.xlsx etc.

If you wish to output to seperate sheets then you would have to use the 'replacing entire path with group option', this method would rely on your create an entire filepath string using a formula tool. Now the different to using this to output to a sheet rather than different workbook would be to do the following (place the sheetname after a PIPE - | post the .xlsx extention:

field 1 URL

Market1 c:\FileName.xlsx|Market1

Market2 c:\FileName.xlsx|Market2

Ben

All comments

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.

Example Workflow.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703