community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to transpose table with unknown number of Columns

Asteroid

Hi,

Im trying to transform a table from  :

IDTimeStampPage
7633909/10/2018 15:34:44Costs
7633909/10/2018 15:34:57Leads
7633909/10/2018 15:40:35Customers
7633909/10/2018 15:44:28Costs
7633909/10/2018 15:44:34Leads
7633909/10/2018 15:45:15Customers

 

To show the flow of pages accessed as :

IDPage1Page2Page3Page4Page5Page6
76339CostsLeadsCustomersCostsLeadsCustomers

 

... and so on.
  

There may be far more page selections per ID but can would the code be able to dynamically create the required output regardless of the Pages?

Also, the table is ordered by ID and Timestamp when loaded.

many thanks and any help is greatly appreciated.

 

Fiorano

Alteryx Certified Partner
Alteryx Certified Partner

Hi @fiorano 

 

Here's one solution:

 

Pageex..PNG

 

 

- Tile Tool to create sequence of pages - Using Unique Value Method by ID

- Multi-Field Formula creates the Page + Number sequence of records - Using Page Field Name + Tile_Seq_Num

- Use Cross-Tab Tool grouping by ID number

 

WF attached on version 2018.4

 

Cheers,

Alteryx Certified Partner

Sure! I've attached an example of how this can be achieved using a Cross Tab tool. Let me know if you have any questions.

Asteroid

Hi 

 

Thanks both for your replies ! @Thableaus your're solution was perfect.

 

One last thing I have been asked to check is after the final table has been created - is there a way to go back through the 'Pages' to see if users moved to the Customers page immediately after hitting the Leads page.  Would it be possible to loop through all the columns and output a table if all ids?

 

IDTimeStampPage
54522019-05-14 15:20:18Leads
54522019-05-14 15:25:40Customers
54522019-05-14 15:32:18Data
54522019-05-14 15:43:22Costs
54522019-05-14 15:47:54Customers
54522019-05-14 15:52:45Data
54522019-05-14 15:59:59Costs
54522019-05-14 16:01:11Leads
63572019-04-24 13:46:12Costs
63572019-04-24 13:50:42Customers
63572019-04-24 13:58:50RawData

 

So, after the above dataset would result in a table with only 5452 in it as this user went directly to Customers after the Leads page?

 

Many thanks again,

 

Fiorano

Labels