Change Capture

[suby]

All,

I have 4 Input Data sets.

Current data - 29-04-2022.

Historic Data
31-01-2022
28-02-2022
31-03-2022

I have (PROD ID , PROD STATUS, PROD STATUS DATE) AND TEST Column is available only in 31-01-2022
( here I'm trying to bring different workbooks with different schema)

The Product Lifecycle status are defined below.

1.Draft
2.Design
3.Development
4.Implemented

PROD STATUS DATE is date field which represents the actual date of the PROD STATUS.

Logic

- On my current Data set (i,e 29-04-2022 ) i want to Identify the WHERE The PROD STATUS = 4. Implemented
take those Product and look back at my historic Data and identify

- How long these 4.Implemented Products where at

1.Draft to 2.Design (No.of Days)
2.Design to 3.Development (No.of Days)
3.Development to 4.Implemented (No.of Days)
and finally Overall from 1.Draft to 4.Implemented (No.of Days)

I have attached the all the Input Data set and Output that is expected. I presume there is a macro is needed in first place to bring multiple workbooks with different schemas.

And also on the logic aspect is there a solution for me to create a macro and just scan through the historic Data set any thoughts or ideas Please.

Thanks

[danilang]

Hi @suby 

Here's one way you can do it.  

For the Excel reader macro, I used the CReW WildCard XLXS Input macro since I had it on hand.  If you don't have it or want to use a different one, google "alteryx excel different schema macro" and you'll find 4 solved posts and a video tutorial on the first results page.  The first dynamic rename adjusts the schema of the file with the extra [test] column  The three Multi-Row Formula tools add the column ID used in the cross tab, the elapsed time and builds the final column header names.  The output looks this.

The final summarization step is left as an exercise for the student

Dan

[suby]

Hi Danilang,

Will give it a try.

Thanks for the quick solution and would it be possible to upload the crew macro as well please since i have restrictions on

down loading that from the link you shared.

Many Thanks

[suby]

Hi Danilang,

In my original input Data set we have one sheet in all the Files.

What if all my Input Data set has multiple sheets within each files and i  want to import a specific sheet and union the data and carryon building your solution.

It would be great if you could show some pointers please as i am stuck.

I have reattached the modified Input Date files for your reference as i am stuck at building the Initial bit of reading multiple files with multiple sheets with different schema and choose a specific sheet to build your solution 

[suby]

Hi Danilang,

Could you please post the workflow with the CREW macro as a zip files please as i tried downloading it but facing issues when i tired to configure

Thanks