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 Manipulation using cross tab and transpose

EoinM824
5 - Atom

Working on data that looks like this

 

EE IDCost CentrePeriodSalaryASalaryBBonusABonusBPension
1AAJan1000 100  
2AAJan2000 200  
3BBJan 1500 150100
7BBJan 2500 250 

 

EE IDCost CentrePeriodSalaryASalaryBBonusABonusBPension
1AAfeb1000 100  
2AAfeb2000 200  
3BBfeb 1500 150100
9BBfeb 4500 2000 

 

EE IDCost CentrePeriodSalaryASalaryBBonusABonusBPension
2AAmar2000 200  
3BBmar 1500 150100
9BBmar 4500 2000 

 

Need it to look something like this:

 

EE IDCost CentrePeriodSalaryASalaryBBonusABonusBPensionPeriodSalaryASalaryBBonusABonusBPensionPeriodSalaryASalaryBBonusABonusBPension
1AAJan1000 100  feb1000 100  mar     
2AAJan2000 200  feb2000 200  mar2000 200  
3BBJan 1500 150100feb     mar 1500 150100
7BBJan 2500 250 feb 1500 150100mar     
9BBJan     feb 4500 2000 mar 4500 2000 

 

Is this possible? I can join the input tables using the union tool, I have transposed using EE ID and Cost Centre as key fields and then cross tabbed again with EE ID and Cost Centre as key fields. This giving me the desired number of rows.Fields EE ID and Cost centre are populated. Struggling to figure out how to manipulate the remaining fields for desired output. any help would be great.

 

Current attempt attached below.

4 REPLIES 4
fmvizcaino
17 - Castor
17 - Castor

Hi @EoinM824 ,

 

In this particular case, you can use the multiple join, it does exactly what you are looking for.

Look at the example and let me know if that works for you

 

Best,

Fernando V.

EoinM824
5 - Atom

Thanks @fmvizcaino 

 

I was exploring this earlier - my issue is that EE ID 9 from the 3rd table does not join the output as it is unique. Would also need the period columns populated if null. I was trying to use a multirow formula but it seems a little tedious!

 

Eoin

RolandSchubert
16 - Nebula
16 - Nebula

Hi @EoinM824 ,

 

I created a workflow using Join Multiple, added a Formula tool to fill NULL values for EE ID and Cost Centre and a Summarize tool to identify the periods, added the using Append Fields then. Does this solve your problem?

 

Best regards

 

Roland

fmvizcaino
17 - Castor
17 - Castor

Hi @EoinM824 ,

 

Attached is an example showing how to do it. I'm not sure if it is the simple way, but it does the job.

Let me know if that works for you.

Best,

Fernando V.

Labels