Creating Continues Coverage Rows

[PCAM]

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.

Initial Question:

From: PCAM 

Sent: Tuesday, July 12, 2016 3:56 PM

To: client services <clientsvcs@alteryx.com>

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.

Thanks! ---------

And the Alteryx Response from Ozzie (thank you!)

Hi Patsy!

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

Best,

Ozzie Dembowski | Alteryx Inc. | Customer Support Engineer

And my response back this morning:

Hi Ozzie!　

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...

Thanks!

PCAM

[JohnJPS]

There may be a simple issue in Alteryx not knowing (without being expressly informed) that the ThruDate of 12/31/2015 should wrap into the FromDate of 01/01/2016.

To fix that, I would suggest sorting and using a MultiRow formula, but surprisingly that didn't work... e.g.

IIF(DateTimeAdd([From Date],-1,"days") == [Row-1:Thru Date],
    [Row-1:From Date],[From Date])

For some reason, that didn't work...

However, if I simply generate the value ahed of time using a regular formula (e.g. a field called "FromDateMinusOne") and then use that to update my "FromDate"...

IIF([FromDateMinusOne] == [Row-1:Thru Date],
    [Row-1:From Date],[From Date])

... then it works... essentially, this just combines those two time frames as having the same (whatever is earliest) start date.

Of course, data must be pre-sorted before doing that Multi-Row formula.

Anyway - this is all in the attached workflow (which assumes your Excel file is in the same directory as the worfklow).  Hope it helps!

[PCAM]

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.

Thanks for your response!

[JohnJPS · Accepted solution]

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.

[PCAM]

I tried the mulit-row formula as suggested and am not getting the result I expect with my live data. Any other thoughts?

[JohnJPS]

Could you take an extract of the data and share, explaining where discrepancies with expectations occur?

[PCAM]

Hi JohnJPS,

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.

Patsy