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

Crosstab records based on column names

aysha6029
8 - Asteroid

Hi Friends,

 

I want to Crosstab every 10 records based on column names.. pls help me with the workflow to achieve the solution.

9 REPLIES 9
aysha6029
8 - Asteroid

Hi Friends,

 

I ll give an example of my problem. i want to cross tab te below table using the column name.. pls help me on how to do this.

 

NameHike
AddressGravette
Telephone444555666
NameMike
AddressGranite
Telephone22288877
NameHancock
DultonM
11 - Bolide

Hi aysha6029,

 

Great data blending question! I assume you want your data to end up like this:

 Name AddressTelephone
 HikeGravette  444555666
 Mike Granite22288877 

 

If so, there's a simple solution! All you need to do is add a Multi-Row formula tool before the Crosstab tool. I attached a workflow that demonstrates the answer.

Capture (3).PNG

 

The Multi-Row Formula is used to create a new field called Counter. Each time "Name" appears, the counter goes up by 1.  So your table data will become:

Field1Field2Counter
Name Hike 1
Address Gravette 1
Telephone 444555666 1
Name Mike 2
Address Granite 2
Telephone 22288877 2
Name Hancock 3

 

Here's the Multi-Row Formula's configuration:

Capture.PNG

 

You can now Crosstab this data grouping on the Counter. Here's the configuration

Capture (2).PNG

 

Tada! Let me know if you have any questions! Please mark if this solves your question!

 

Thank you,

DultonM

aysha6029
8 - Asteroid

Thank You. Its working

aysha6029
8 - Asteroid

Hi Friends,

 

I ran the Workflow and generated grouping numbers to transpose the records in proper order. My requirement is to neglected all the Result with Error ( Grouping 2) and take only the Success Records ( Grouping no 1 and 3) and transpose them to a excel sheet. Please help me to solve this problem.

 

ResultSuccessGrouping
NameMike1
AddressGravette1
ResultError2
Error DetailError out2
ResultSuccess3
NameHarrison3
AddressJoplin3
DultonM
11 - Bolide

Hi aysha6029! Will The Success field contain the word "Error" whenever that row failed? If so, one way to accomplish this is to....

 

1) add a Summarize tool. Group by on [Grouping] and Concatone [Success]. This collapses Success column down to 1 row per grouping.

2) Then use a Filter tool with the Contains function to identify all groupings that contain the word "Error".

3) Lastly, use a Join tool to join the original data (before the Summarize) with the false output from the Filter on the field "Grouping".

 

What comes out of the J output of the join will be only the groupings that didn't have "Error" on any of the rows! You can then transpose and output from there.

 

Hope this helps!

SeanAdams
17 - Castor
17 - Castor
Hey @PANG,

Assuming that you know that this data always has 3 rows (name; Address; Telephone) - then what you can do is use an iterative macro (there are less complex ways but this will be quite quick):

- Take the first 3 rows

- Transpose and then cross-tab to get it into the outcome format

- Filter out rows that have null in the "Name" column

- Send all but the first 3 rows to the iterator


This does feel like a different (but exciting) challenge than @aysha6029 raised - so it would be well worth raising this as a new new query / thread, and mocking this up with some test data and your desired outcome (similar to how @JoeM does with the weekly challenges here: https://community.alteryx.com/t5/Weekly-Challenge/Weekly-Challenge-Index-amp-Welcome/td-p/48275), and we can work as a community to get you to a solution to your problem.

Looking forward to seeing your challenge - happy to mock this up in Alteryx once you've put it together.

Cheers
Sean
PANG
6 - Meteoroid

Hi DultonM,

 

Thanks for the explanation, I am encountering similar problems and I found this very helpful.

However, my problem is more troublesome. If I can refer to aysha6029's example, my table would look like below:

 

Field1Field2Field3Field4
Name HikePeterLance
Address GravetteLondonDC
Telephone 444555666121131324242
NameAlaric  
AddressANZ  
Telephone989876778  
Name MikeRonald 
Address GraniteIndonesia 
Telephone 22288877798992 

 

As you can see, each section has different number of columns. I am wondering if there is a way to transpose this table into the ideal form, which would be

NameAddressTelephone
HikeGravette 444555666
PeterLondon1211313
LanceDC24242
AlaricANZ989876778
MikeGranite 22288877
RonaldIndonesia798992

 

Thank you,

PANG

PANG
6 - Meteoroid

Hi Adams,

 

Thanks for the suggestion. I have raised a new thread for the same topic.

But as I am pretty much a rookie, do you mind to elaborate on your method to help me understand better?

 

Thanks,

PANG

SeanAdams
17 - Castor
17 - Castor
Absolutely - no problem at all @PANG


* Hop onto the discussion board here: https://community.alteryx.com/t5/Data-Preparation-Blending/bd-p/data-preparation-blending

* Hit the button titled "New Topic", and that should give you the opportunity to create a new topic.

* Given that your particular case is unique, it's worth describing the challenge explicitly (that you have data from different sources which have a common 3 row structure in common, but they differ in number of columns, but all need to be brought into a single structure)

* If you can put a simple Alteryx canvas together with your sample data embedded in Text Inputs; and your expected output embedded in a text input; and attach this to your new topic - then it's really quick for community members to whip up a response that directly addresses your specific need.

Have a look at one of Joe's weekly challenges to get a sense of this - here's a link to a sample:
https://community.alteryx.com/t5/Weekly-Challenge/Challenge-8-Aggregate-Consumer-Purchases/m-p/36735

Thanks @PANG
Sean
Labels