I have a data in the below format LocationBurnit UnitNFE Date2-Feb-14 WorkRepair TypeXDF BrandPoly ID 12EE4134UGrade 21densityat 450L09kg2144 at 300M09ml12002344compositionCCD12% 1 BME44%10.5 I would like to restructure it to the format below so that i can make better analysis out of it LocationUnitDateWorkTypeBrandIDGradeMetriclevelcodeMeasurereadinglowerupperBurnitNFE2-Feb-14RepairXDFPoly12EE42densityat 450L09kg21 BurnitNFE2-Feb-14RepairXDFPoly12EE42densityat 300M09ml1200 BurnitNFE2-Feb-14RepairXDFPoly12EE42compositionCCD12% 1BurnitNFE2-Feb-14RepairXDFPoly12EE42compositionBME44%1 BurnitNFE2-Feb-14RepairXDFPoly134U1densityat 450L09kg44 BurnitNFE2-Feb-14RepairXDFPoly134U1densityat 300M09ml2344 BurnitNFE2-Feb-14RepairXDFPoly134U1compositionCCD12% 1 BurnitNFE2-Feb-14RepairXDFPoly134U1compositionBME44%0.5 Note: I have many such tables stored as multiple tabs within an excel sheet. My workflow needs to surf through each tab and do the above restructuring process and finally append all the data together into a single table. I would like to know if such a restructuring process is possible in Alteryx. Any suggestions? Thanks

Data restructuring in Alteryx

I have a data in the below format

Location	Burnit
Unit	NFE
Date	2-Feb-14
Work	Repair
Type	XDF
Brand	Poly
ID				12EE4	134U
Grade				2	1
density	at 450	L09	kg	21	44
	at 300	M09	ml	1200	2344
composition	CC	D12	%	<1	>1
	BM	E44	%	1	0.5

I would like to restructure it to the format below so that i can make better analysis out of it

Location

Unit

Date

Work

Type

Brand

Grade

Metric

level

code

Measure

reading

lower

upper

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

12EE4

density

at 450

L09

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

12EE4

density

at 300

M09

1200

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

12EE4

composition

D12

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

12EE4

composition

E44

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

134U

density

at 450

L09

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

134U

density

at 300

M09

2344

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

134U

composition

D12

Burnit

NFE

2-Feb-14

Repair

XDF

Poly

134U

composition

E44

0.5

Note: I have many such tables stored as multiple tabs within an excel sheet. My workflow needs to surf through each tab and do the above restructuring process and finally append all the data together into a single table.

I would like to know if such a restructuring process is possible in Alteryx. Any suggestions? Thanks

Best Practices

Transformation

Dynamic Processing

Iterative Macro

Accepted answers

BenMoss

Hi!

Here is a sample workflow that restructures your data.

If you wish to process multiple tables through this workflow you should perhaps consider a batch macro. Or you could ammend the workflow and group by a 'tableID' at each stage.

This should give you a good starting point though.

Ben

Restructure table.yxmd

All comments

haroon_sa

@GaRaGe

you can try append fields and configure it as your need.let me know if it works or not

Hakimipous

@GaRaGe

As for the first part of the table it's doable quite easily.

but then I can seem to see any logic in the rest of the data on how it is aligned (strarting from ID)..... In such case I would deal with them manually, but then that can't be as dynamic as you want

Here is the workflow on how I dealt with the first part of the table, hope that helps a bit

pivot.yxmd

BenMoss

Hi!

This should give you a good starting point though.

Ben

Restructure table.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703