Data Transformation for attached use case

[timewaste]

Hello Peers,

Sorry if this is a big ask but I would like to request a solution for following use case attached below. I have tried designing a few workflows on my end with Transpose and Cross Tab approaches over last 2 weeks (pretty much same time I have started using Alteryx) but in vain. Appreciate any support in accomplishing this. TIA!

Looping in Gurus...

@MarqueeCrew @Kenda @JoshKushner @jdunkerley79 @Joe_Mako @JohnJPS 

[vishwa_0308]

Hi,

I have distributed your file into 4 tables like  ORG, SP, Responsibilities, Metadata and then done some calculation to match your look a like output.

Please find the sample attached.

Best,

Vishwa

[Joe_Mako · Accepted solution]

Attached is one route, I am sure it could be optimized, but I felt this was a good balance for future flexibility, and mostly dynamic.

[JoshKushner · Accepted solution]

Here's one that doesn't split the data very much. It should still be flexible and future-proof (assuming there aren't any more types of items like the 'SP' ones)

Flow:

Results:

Transpose:

Cross Tab:

Multi Row:

[jdunkerley79 · Accepted solution]

Here is my go. Substantially similar to @Joe_Mako:

[timewaste]

@jdunkerley79 @JoshKushner @Joe_Mako @vishwa_0308

Appreciate it so much guys!!! I think I came close to two of these approaches by looking at these. Thanks for your time.

[timewaste]

@jdunkerley79 A question from your approach, instead of SP1,SP2,SP3.... so on... ,if I come across data more so like this below.. I mean it doesnt start with 'SP'.

What I gave initially:

SP1	14.4	1.6	16.0
SP2	14.4	1.6	16.0
SP3	10.8	1.2	12.0	12.0
SP4	10.8	1.2	12.0	12.0

what if the source data looks like:

PH	14.4	1.6	16.0
AH PHO AH	14.4	1.6	16.0
SAT TDM SAT	10.8	1.2	12.0	12.0
MON PH ADFD	10.8	1.2	12.0	12.0

How do I change the expression in REGEX_MATCH? Do I need to add a prefix 'SP' on the fly and deal with it? TIA!

[Joe_Mako]

You could have it match on ORG instead of SP, like:

REGEX_MATCH([Field1],"ORG\d")

[jdunkerley79]

I'd probably do as @Joe_Mako said (as I like RegEx). But could also do:

STARTSWITH([Field1],"ORG")

[timewaste]

@jdunkerley79  Another followup question using your workflow... how I can address if I have additional columns coming in the source sheet (other than the org columns as shown below). I have highlighted them in red here below too. Immense thanks once again.

Input Data:

ORGS	SPCAT	PRID	VID
ORG1				O11	O11	O21	O21
ORG2				O12	O12	O22	O22
ORG3				O13	O13	O23	O23
ORG4				O14	O14	O24	O24
ORG5				O15	O15	O25	O25
ORG6				F	A	F	V
Subphases
PH	HW1	3	0	14.4	1.6	16.0
AH PHO AH	SW1	3	0	14.4	1.6	16.0
SAT TDM SAT	FW1	3	0	10.8	1.2	12.0	12.0
MON PH ADFD	REW1	3	0	10.8	1.2	12.0	12.0
Responsibilities
R1				YES	YES	NO	NO
R2				NO	NO	YES	YES
R3				NO	NO	NO	NO
R4				YES	YES	NO	NO
R5				NO	NO	YES	YES
R6				NO	NO	NO	NO
R7				YES	YES	NO	NO
R8				NO	NO	YES	YES
R9				NO	NO	NO	NO
Metadata
M1				7.50	7.50	7.50	7.50
M2				82	82	82	82
M3				1715	1715	1715	1715
M4				0	0	0	0
M5				0	0	0	0
M6				0	0	0	0

Output Data: It would still look the same but with these new 3 additional columns in the output too...