Complete novice - List all values for unique value

[noobteryx]

DATA:

I have a set of data that is showing transaction times for a particular process for a number of users. i currently have 1 day and approx 200k rows and data is structured as follows:

Objective:

I want to be able to see the average / distribution of the time it takes by CNTR_NBR - e.g. what is the example time "Exam" vs "BUNC" takes but equally the distribution / extremities. Equally a separate goal (but not yet) would be is Georgeweasley slower at processing "BUNC" than "Ronweasley" is... 

Question:

I have been able to trim my cntr_nbr (as the original data actually has a number of none required characters after each customer), I have been able to add processing time using multirow formula and expression:

tonumber (
IF [USER_ID]=[Row-1:USER_ID]
Then DateTimeDiff([BEGIN_DATE],[Row-1:BEGIN_Date],"seconds")
ELSE '0'
ENDIF )

In order to achieve my objective i think my option is:

Pivot, list or transpose my data so that I have each unique CNTR_NBR as a new column, and show ALL processing time values in the rows beneath, then I can analyse each column separately. 

However i am struggling to cross tab / transpose to LIST all values not summarise?

i.e. below is the current progress I have made - as you can see I have each unique CTNR_NBR as a column header, and I have listed all my values but because it is a concatenate its no use to me currently.....

Equally if i am missing an easier method i would appreciate any advice! 

[noobteryx]

Update:

I added a row ID and then crosstab using this and have broadly got it to work (see picture below):

If possible i would like to remove the nulls, and also somehow add a formula to remove values >X and <Y......

[danilang · Accepted solution]

Hi @noobteryx (great user name...but what are you going to when you become an ACE)

Judging from your workflow, you already have to change your name.  Using the Cross Tab tool will automatically award you house points.  Here's some more tips

You can do most of the analysis of the first part of question by using the functions available in the Summarize tool.  This frees you from having to duplicate formulas to generate your stats for each column after you Crosstab.  I've configured it to generate average, min, max, standard deviation, and the 25th, 50th and 75th percentiles. 

For the user based rankings, use the cross tab with the users as rows giving

Note: that I also modified your calculation of the Processing time to remove the noise values when you move from one user to the next.  

Dan

[noobteryx]

Hi @danilang, Firstly thank you for such a quick and helpful response! 

I hadn't realised I could just use summarise instead of some of the histogram tools so thanks for that. Equally using summarise instead of crosstab was significantly simpler - think i had set off on the wrong track trying to use crosstab! 

Regarding the use of crosstab you mention below for showing by user helped massively, I downloaded your example applied to mine and I think I have completed it!