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

29-04-2022.xlsx

31-01-2022.xlsx

28-02-2022.xlsx

Ouput.xlsx

31-03-2022.xlsx

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

ChangeCapture.yxmd