Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data Preparation : Cross tab

harsha384
8 - Asteroid

Hi,

I was looking for a solution if its possible in Alteryx.

Source data :

     FY 2015FY 2015
    Cal. Year/monthJan-2015Jan-2015
Col ACol BCol CCol DCol ENet SalesQuantity
1XYZAAABCZ$88760
1XYZAAABCW$7,675120
1XYZAAABCGHIZ$2715
1XYZAAABC GHKZ$4613

 

Required Output :

Col ACol BCol CCol DCol E Cal. Year/month  
1XYZAAABCZFY 2015Jan-2015Net Sales$887
1XYZAAABCZFY 2015Jan-2015Quantity60
1XYZAAABCWFY 2015Jan-2015Net Sales$7,675
1XYZAAABCWFY 2015Jan-2015Quantity120

 

Please let me know if a solution can be provided to it.

 

Thanks

Harsh

3 REPLIES 3
SophiaF
Alteryx
Alteryx

Hi @harsha384,

 

You will want to use the Transpose tool to acheive this. A couple prep steps will be needed:

     1. You will need to create a column named "Cal.Year/month" with the appropriate month. This could be done dynamically in the workflow, or you can manually do this with a Formula tool.

     2. You will use the Transpose tool to pivot the data. You will want to select the fields to "pull through" as your Key Fields, and fields you want to pivot as your "Data Fields"

 

transpose.jpg

 

Prepped data coming into the Transpose:

transpose1.png

 

Results after Transpose:

2.jpg

Sophia Fraticelli
Senior Solutions Architect
Alteryx, Inc.
Joe_Mako
12 - Quasar

How about the attached workflow?

 

I built this with the assumption that there would be additional pairs of columns (Net Sales and Quantity) for each month, and the number of attribute fields (Col A - Col E) will not change. It will continue to work as more column pairs are added, but will need modification if the number of attribute fields changes.

 

It splits off the first two header rows from the data, then splits off the attribute fields from the pairs of columns, transforms each as needed, joins the streams back together, and renames the attribute fields.

 

Reshape

harsha384
8 - Asteroid

Thanks for the solution. I have a another case :

 

INPUT is like :

 

    Curreny Year Profit   CURRENY YEAR SALE    
ProductJANFEBMARCHAPRILMAYJUNEJANFEBMARCHAPRILMAYJUNE
A                                1           1              1                          1              1             1              1                                1             1           1              1             1
B                                2           2              2                          2              2             2              2                                2             2           2              2             2
C                                3           3              3                          3              3             3              3                                3             3           3              3             3
D                                4           4              4                          4              4             4              4                                4             4           4              4             4
E                                5           5              5                          5              5             5              5                                5             5           5              5             5
F                                6           6              6                          6              6             6              6                                6             6           6              6             6

 

OUTPUT should be :

 

 

ProductKPIMonthValues
ACurreny Year ProfitJAN              1
BCurreny Year ProfitJAN              2
CCurreny Year ProfitJAN              3
DCurreny Year ProfitJAN              4
ECurreny Year ProfitJAN              5
FCurreny Year ProfitJAN              6
ACurreny Year ProfitFEB              1
BCurreny Year ProfitFEB              2
CCurreny Year ProfitFEB              3
DCurreny Year ProfitFEB              4
ECurreny Year ProfitFEB              5
FCurreny Year ProfitFEB              6
ACURRENY YEAR SALEJAN              1
BCURRENY YEAR SALEJAN              2
CCURRENY YEAR SALEJAN              3
DCURRENY YEAR SALEJAN              4
ECURRENY YEAR SALEJAN              5
FCURRENY YEAR SALEJAN              6
ACURRENY YEAR SALEFEB              1
BCURRENY YEAR SALEFEB              2
CCURRENY YEAR SALEFEB              3
DCURRENY YEAR SALEFEB              4
ECURRENY YEAR SALEFEB              5
FCURRENY YEAR SALEFEB              6

 

Is it possible to solve it? Please guide!

 

Thanks and regards

Harsh

Labels