This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi! I'm a fairly new Alteryx user (7 months now) and I have run across a problem I can't seem to figure out. I sent the following request in an email and this is the thread so far. My case number from Alterys Support is 116530.
The problem is: How do I get a single coverage row when there are no changes in the data elements, but separate coverage rows if there are any changes in the data elements. There are five, so thousands of conbinations are possible.
Sent: Tuesday, July 12, 2016 3:56 PM
To: client services <firstname.lastname@example.org>
Subject: Capturing Continuous Coverage Rows
Good afternoon, I am working through a challenge and I need some help. We are working with a DB2 database. I have a table which records year over year changes for certain items. I need to pull data from this table so that I can capture any changes that occur in the customer data. However, if year over year, there are no changes in a specific set of data elements, then I need to capture earliest and latest dates for that customer. In other words, I need a single line of data instead of a line of data for each year.
I have 5 data elements to look at, and if there are no changes in those 5 elements, then I need min/max dates. But, if there is any change in any one ore more of those elements, then I need to capture min/max date for that change.
I am attaching a spreadsheet of the sample data. Table 1 represents my raw data and Table 2 represents what the data should look like with the min/max dates captured for each change.
I have tried using a summary tool with group by on the elements and capturing min/max date that way, but I am losing rows when I do an outer join into the main dataset. Any thoughts would be most welcome.
And the Alteryx Response from Ozzie (thank you!)
Yes, your example was clear and I made a workflow that has the expected outcome. Except I have a correction to make from your excel. For contact name: Kirk, James T. The PO# doesn’t change so therefore it should only be two rows. However you said you performed this before and you were getting dropped records from a join?
Summarizing coverage rows.yxmd
Ozzie Dembowski | Alteryx Inc. | Customer Support Engineer
And my response back this morning:
Thanks so much for your quick response.
Unfortunately, I was not able to run the workflow as I am still on 10.1. A co-worker ran it for me as she is running 10.6.
James T is a perfect example of my problem. He should have 3 rows.
A row for PO 65651 and Part # 101 effective 1/1/2015 thru 11/30/2015
A row PO 65651 and Part # 100 effective 12/1/2015 thru 1/31/2016
A row for PO 65651 and Part # 101 effective 2/1/2016 thru current (12/31/400 represents no end date)
This is exactly the row I'm having the most difficulty with.
Per request, I'm going to post our discussion to the community. Working on that now...
This is an interesting thought. I have five data elements that may or may not change, and I can have up to a dozen rows for a single Contact Record ID. If any one of the data elements changes, then I need to capture that change and the from/thru dates when that change is true.
I think I could work that out in the multi-row formula as an If Then ElseIf set of statements.
The MultiRow formula in the workflow groups by basically everything in the file; similarly for the Summary tool. These groupings should apply the breakdowns by all those various data elements, and hopefully eliminate the need to manully hard-code If-Else statements.
Sorry for the delay in my response. I really don't have much to add to my initial Excel sheet. I think I have the problem resolved though. I did a series of multi-row formulas that basically forced my group bys. Then I used your idea at the tail end of the set to identify the rows I needed to keep and adding a null date to the rows I could remove. That and a filter, and ta dah!
The data I provided is a tiny subset. The actual data is over 60,000 rows, coming out of DB2, and a single person can have multiple rows of data with or without changes.
I've been working on this problem for two or three months, so I really want to thank you for your idea. Without it I would not have gotten to the end product.