Need help creating new rows based on criteria

I have a data set that contains a list of customers that have different "Tiers". I usually sort the list by Customer Number and Tier to group like items. I need to create a workflow that will insert two new rows when the Tier changes from one row to the next. I mocked up a dummy file (attached) and highlighted the new fields that I want to created based on tier changes for each customer. I'm sure I use the "Generate Rows" tool, but I'm not sure how to implement. Can anyone help?

Sample.xlsx

Datasets

Accepted answers

All comments

Brankl

How many tiers are there? My first thought is to filter down to each tier separately and add two rows to the bottom of each table before unioning them back together. If there are a ton of tiers, you could use a macro instead of manual filters.

ChrisTX

See if the attached workflow helps

Screenshot 2024-01-04 113046.png

Chris

Need help creating new rows based on criteria.yxmd

cjaneczko

Another solution.

Two New Rows.yxmd

CoG

Another another solution:

Sample Workflow.yxmd

ChrisTX

@CoG in the Sort tool, sorting by Customer Number and Tier doesn't guarantee that the two New rows will be placed at the bottom of the group.

The sort order may be correct today, but may not be correct after a software version update, or a change to the AMP engine, or some other future software change.

CoG

@ChrisTX- is absolutely correct. Here is another x3 version if the order is critical:

_Main 2.png

Sample Workflow.yxmd

RCern

@ChrisTX @CoG Thank you both so much. Sorry for the delay, but the holidays had me off of work. I implemented the proposed workflow, thanks so much. A couple of changes have been requested. Now, I am being asked that for all Plan Codes, create one summary row for each unique combination of Customer Number, Billing Location, Class Number, Plan Code, Plan Choice Coverage Tier, Month and Adjustment Indicator.

Issues:

I think I was able to do that with the attached workflow, however, in the summary lines some of the fields are not being populated like in the first 8 columns.
For the summary rows, they would like to sum the Benefit Covered Payroll, Gross Premium Amount (Where the adjustment indicator = "n", Withheld Admin Fees, Withheld commissions, premium adjustments, net premium remittance for the lines above the summary line.

Any advice or guidance would be greatly appreciated!

Grouping.yxmd

ChrisTX

@RCern the YXMD file you shared does not include the input file "Newtest - Copy.xlsx"

Please use the menu option "Options > Export workflow" to include the input file, then share the YXZP file

RCern

@ChrisTX Is this better?

Grouping.yxzp

ChrisTX

You mentioned: "in the summary lines some of the fields are not being populated like in the first 8 columns."

After the Summarize tool, the first 8 columns are:

Last_TPA_Group_Reference_Number
Last_Customer_Name
Customer_Number
Billing_Location
Class_Number
Plan_Code
Plan_Choice
Last_DHMO_State

The last two columns for Plan_Choice and Last_DHMO_State are blank in the Summarize output.

But if you look at the data going into the Summarize tool, all 5 data rows have a blank value for Plan_Choice and DHMO_State

For the fields you want to Sum, just use the Sum action.

You may need a Filter tool before or after the Summarize to filter for adjustment indicator = "n"

But then you need to figure out what you want to do with the rows where adjustment indicator not equal to "n"

Screenshot 2024-01-17 132406.png

Have you completed the Interactive Lessons under Academy? Do you just need help with the basic functionality of the Summarize or Filter tools?

Or maybe I'm just not understanding the challenges with this data prep.

Chris

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703