Help with Adding New Rows by Finding Max Date, Adding Date+1 and Duplicating Columns

[Phyllis]

Hi! How can I go about finding the max date for every User ID and then adding an additional row each User ID, date+1, and a cost of 0, while duplicating the rest of the columns (Cost Structure and Other Column).

Example of original:

Date	User ID	Cost Structure	Other Column	Cost
01/25/2016	123	CPM	1	100.00
01/25/2016	124	CPM	1	150.00
01/25/2016	125	CPM	1	200.00
01/25/2016	126	CPM	1	800.00
01/20/2016	127	CPM	1	50.00

Into this output (all the new rows in red), row order does not matter:

Date	User ID	Cost Structure	Other Column	Cost
01/26/2016	123	CPM	1	0
01/26/2016	124	CPM	1	0
01/26/2016	125	CPM	1	0
01/26/2016	126	CPM	1	0
01/25/2016	123	CPM	1	100.00
01/25/2016	124	CPM	1	150.00
01/25/2016	125	CPM	1	200.00
01/25/2016	126	CPM	1	800.00
01/21/2016	127	CPM	1	0
01/20/2016	127	CPM	1	50.00

Thank you!

[Philip]

Here's one way. Basically, it sorts the data by UserID and date descending, the Unique tool takes the first record for each UserID, add a day with all the information in the other columns retained, set cost to 0, and merge back with the original data. The workflow is attached to see how tools are configured.

[MarqueeCrew · Accepted solution]

@Phyllis,

I've attached a workflow that adds the data.  I use a summary tool to Group by the User ID and finds the maximum date.  I also group by the cost structure and other column.  If the values for these fields is a constant, you can create them in the next formula tool.  That's where I set Cost to be 0.

In the formula I deal with your dates to keep them in your non-ISO standard format.  I convert them to the ISO format, add a day, then convert them back.  I then union the original data together with the new rows.

Check it out and if you need more help, we're here to help.

Cheers,

Mark

[Phyllis]

Hi Mark,

This worked great. Thanks!

[Philip]

Hi Phyllis

As an FYI, the reason I went with Sort + Unique tools instead of the Summarize tool is that once sorted by date, the unique will take the last values of the other columns when getting the last entry for the User ID. By grouping on the other columns, the Summarize tool will find all the unique combinations of those other columns and you'll have multiple entries as the last record for each User ID.

Either way works fine, it's just what you want as the records you'll be adding. If you only want one record for each User ID with the values of the other columns as they were in the last entry, the Unique tool will get you that. If you want all the combinations of the other columns for each User ID, the Summarize tool with grouping will give you those.

Just make sure you're getting out what you are wanting.

[Phyllis]

Ah, thanks Philip. Going to find time to QA the versions I incoporated and may incorporate your solution if it isn't what I need. Thank you for taking the time to respond!!!