Data Transpose/CrossTab with date as header

[aksharma89]

Hi Team,

I am working on a scenario where I need to transpose the data in following way :

START_DT	PRODCUT	COUNTRY	REGION	ID1_qty	ID2_qty	OD1_qty	OD2_qty	SH1_qty	SH2_qty
10/25/17	ABC	USA	AMR	5	6	2	3	5	6
10/25/17	DEF	CHINA	CHN	2	2	1	1	2	2

Expected data :

PRODCUT	COUNTRY	REGION	input_dt	output_dt	ship_dt	input_qty	output_qty	ship_qty
ABC	USA	AMR	10/25/17	10/25/17	10/25/17	5	2	5
ABC	USA	AMR	10/26/17	10/26/17	10/26/17	6	3	6
DEF	CHINA	CHN	10/25/17	10/25/17	10/25/17	2	1	2
DEF	CHINA	CHN	10/26/17	10/26/17	10/26/17	2	1	2

where ID1_DT is start_dt and and ID2_DT is start_dt + 1 similarly for output_Dt also

Can you please help to suggest a way to achieve this.

[jdunkerley79]

I suggest the following:

First, parse the date (from MM/dd/yy into Alteryx format).

Next, transpose the data so ID1_qty, ID2_qty, etc become rows.

I then used REGEX to split the names to ID/OD/SH and 1 or 2

Next cross-tab to get ID, OD and SH as columns but split as 2 rows (1 and 2)

Then just a case of tidying up. I used a formula tool to adjust the date for the 2 rows and then a couple of formula tools to clone the date column

Finally a select tool to rename to match required output

Sample attached

[aksharma89]

Hi jdunkerley79,

Thank you so much for your response.This is what I was searching for.But the issue am facing is my Regex operation is not behaving correct when by input fields are going 10.

In my business scenario I have 14 input ,14 output,14 ship values.And my outcome is only able to result till 9 values only.

Regex in your wf: (..)(\d).*

Issue : 

Input_Date  fieldname

----------  --------------------

2017-10-25  ID1

2017-10-25  ID10

2017-10-25  ID11

2017-10-25  ID12

2017-10-25  ID13

2017-10-25  ID14

2017-10-26  ID2

2017-10-27  ID3

2017-10-28  ID4

2017-10-29  ID5

2017-10-30  ID6

2017-10-31  ID7

2017-11-01  ID8

2017-11-02  ID9

As soon as ID10 came the regex did not splitter as expected.

Attached the workflow for the below data.

Can you please suggest correction to the regex expression .

Thanks,

Atul

Date_published

Vendor

Vendor_Site

Program

PO_type

Region

Part_number

Model

MLB

Capacity

Color

Wk1_Priority

Qty

Wk2_Priority

ID1

ID2

ID3

ID4

ID5

ID6

ID7

ID8

ID9

ID10

ID11

ID12

ID13

ID14

OD1

OD2

OD3

OD4

OD5

OD6

OD7

OD8

OD9

OD10

OD11

OD12

OD13

OD14

SH1

SH2

SH3

SH4

SH5

SH6

SH7

SH8

SH9

SH10

SH11

SH12

SH13

SH14

Remarks

10/25/17

MPGM

KS

NX

Rep

AMR

xxxx-xxxxxx

A1XX

B3X

16GB

A(GRAY)

p1

50

0

30

20

0

0

0

0

0

0

0

0

0

0

0

0

3

17

19

0

9

2

0

0

0

0

0

0

0

0

0

39

0

9

2

0

0

0

0

0

0

0

0

0

[jdunkerley79 · Accepted solution]

Change the regex to  (..)(\d+).*  and then it should work.

[aksharma89]

Hi  jdunkerley,

Thank you so much for your help .It really helped me a lot to solve my Problems statement!!

Regards,

Atul