topic Re: How to transpose table with unknown number of Columns in Alteryx Designer Desktop Discussions

How to transpose table with unknown number of Columns

fiorano — Thu, 09 May 2019 19:07:30 GMT

Hi,

Im trying to transform a table from :

ID	TimeStamp	Page
76339	09/10/2018 15:34:44	Costs
76339	09/10/2018 15:34:57	Leads
76339	09/10/2018 15:40:35	Customers
76339	09/10/2018 15:44:28	Costs
76339	09/10/2018 15:44:34	Leads
76339	09/10/2018 15:45:15	Customers

To show the flow of pages accessed as :

ID	Page1	Page2	Page3	Page4	Page5	Page6
76339	Costs	Leads	Customers	Costs	Leads	Customers

... 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

Re: How to transpose table with unknown number of Columns

Thableaus — Thu, 09 May 2019 19:12:51 GMT

Hi @fiorano

Here's one solution:

- 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,

Re: How to transpose table with unknown number of Columns

CharlieS — Thu, 09 May 2019 19:16:24 GMT

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.

Re: How to transpose table with unknown number of Columns

fiorano — Wed, 15 May 2019 17:12:02 GMT

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?

ID	TimeStamp	Page
5452	2019-05-14 15:20:18	Leads
5452	2019-05-14 15:25:40	Customers
5452	2019-05-14 15:32:18	Data
5452	2019-05-14 15:43:22	Costs
5452	2019-05-14 15:47:54	Customers
5452	2019-05-14 15:52:45	Data
5452	2019-05-14 15:59:59	Costs
5452	2019-05-14 16:01:11	Leads
6357	2019-04-24 13:46:12	Costs
6357	2019-04-24 13:50:42	Customers
6357	2019-04-24 13:58:50	RawData

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