Comparing Two Sets of Excel Data to Show Integration and Identify Disconnects

[JSantos17]

Hello,

I am trying to compare two sets of Excel data in the following format below. The first data set drives the other to main integration. However, with data set 2 the way that it is exported from its tool. It stacks the CA at one row and the WP are followed underneath it. 

My end goal with this workflow is to group the CA with it's associated WP, but also insure that there is integration between the two data sets and that if there are any disconnects it would be highlighted. Below is also a screen shot of my current workflow build.

I used the "Select" functions to convert the vString in data set 1 and 2 to dates ( as well as rename the columns). I have tried using the "Join" function but since the format of data set 2 is not the same as Data set 1 that was unsuccessful. So using the Union function is what I thought would be the best since they are in somewhat a similar structure and just need to be further massaged. 

Any help would be greatly appreciated. 

Data set 1:

CWBS	CA	WP	Baseline Start	Baseline Finish	Start	Finish
1.1	CA
1.1	CA	ABC1	1/1/2019	2/28/2019	1/1/2019	2/28/2019
1.1	CA	ABC2	2/1/2019	3/29/2019	2/1/2019	4/26/2019
1.1	CA	ABC3	4/1/2019	3/4/2020	4/1/2019	3/4/2020

Data set 2:

CA/WP	Description	Baseline Start	Baseline Finish	Forecast Start	Forecast Finish
CA	ABC
WP1	ABC1	1/1/2019	2/28/2019	1/1/2019	2/28/2019
WP2	ABC2	2/1/2019	3/29/2019	2/1/2019	4/26/2019
WP3	ABC3	4/1/2019	3/20/2020	4/1/2019	3/4/2020

[Thableaus]

Hi @JSantos17 

Would this work?

Flow attached.

Cheers,

[JSantos17]

Hello Thableaus,

Thank you for your response and help. I was able to incorporate some of the functions from your workflow into mine that could help in solving my workflow. I am getting stuck with separating the CA/WP column into WP like what you provided. Since the CA/WP names aren't as simple as "CA1, WP1, etc."

Would using the Formula function work the same if the names of the CA/WP are more detailed? (i.e. T3-1U745_CA_DE_1304JEON_19)? Note that there are various names for CA and WP throughout the file.

[Thableaus]

@JSantos17 

Do the names follow some logic? How do you split CA and WP? If they have some logic or even a vlookup table to designate each of them, it would be much easier.

Cheers,

[JSantos17]

Hello Thableaus,

Yes, the second data set follows this kind of naming logic SAPProject#_CA_CADescription (i.e. TRNG1_CA_PM1, TRNG1_CA_AIRFRM4, etc.) The WP would be similar sometimes involving a concatenation of the CWBS and WP (i.e. 1.3.4.15.DE_ISIM_BMOD_UX).

The data set I am able to control (data set 1) is pulled from MS Project so I am able to input the necessary inputs into custom fields. That allows me to export them into separate columns that I pick. Data set 2 is from another group who uses standard reports from COBRA that is why it is grouped all in one column like I shown above.

Hopefully this information makes it clearer, if not please let me know.

[Thableaus · Accepted solution]

@JSantos17 

Try this flow attached.

I changed the logic to accept a REGEX Match following this pattern: (anything_CA_anything) - if a record has this pattern, then it goes to CA Column.

Else, it goes to WP column.

I'm not so sure how your data looks like, but this is a way to go.

[JSantos17]

Hello @Thableaus,

Thank you very much for your help with this! This workflow was able to work with the format we are using.

I did have a question though after I have the two data sets run through the Join.

The end results shows the Dates in two different formats- Data set 1 - MM/DD/YYYY and Data set 2 - YYYY-MM-DD (on four columns) would using the DateTime function before the Join be the most effective method or afterwards? I am seeing I can really only use the DateTime function for one column instead of multiple.

[Thableaus]

@JSantos17 

You can use DateTime function with multiple columns using Multi-Field Formula tool.

But yes, I think it'd be the best to standardize the one out of the patterns first and before the Join, if possible.

You can include in a Formula Tool.

Cheers, 

[JSantos17]

Hello Thableaus,

I was able to get my workflow to run and compile the information I need. However, I am running into an issue with date format output. I took your advice on using the multi-field formula to get it into a better format before the Join. If you look at the screenshot below the difference between IMS Baseline and COBRA Baseline dates are different. IMS has 4/2/2018 and the COBRA has 04/30/2018. Am I missing something that is causing this?

Ultimately, I am trying to take this flow a step further by adding in another column at the end that will compare these fields so that function will come after the Join.

Thank you for your help in advance.