One of the most commonly asked questions I get in training
is how to show distinct rows inside a Data Table. People need the full set of rows for most of
the analysis, but may also want to be able to display a table with just
distinct values.
Assume you have a simple data set like the following:
And in at least one location, you want to show only distinct
rows, as shown below:
What you need to do is create a new Data Table , which takes
your original Data Table as the source and then pivots it.
To start, you goto File > Add Data Table.
You will then choose the source type to be ‘Existing Data
Table in my Analysis’ (shown as Step 1 in the figure above), then if you have
more than one Data Table loaded, make sure you select the right Data Table
(shown in Step 2 in the figure above), and then select the ‘Pivot’ Transformation and click ‘Add…’ (shown
in Step 3 in the figure above). The Pivot Data dialog will appear.
The ‘Row identifier’ should be the first column in the data
set, (or the key column).
The ‘Column titles’ and ‘Values and aggregation methods’ should
both be ‘None’.
The ‘Transfer columns and aggregation methods’ should list
all the remaining columns in the original Data Table, with the expression set
to UniqueConcatentate for all columns.
You can then set the ‘Transfer column naming pattern’ to %T (which just references the Column name and
no aggregation method).
What this does is not really pivot the data, rather we use
the pivot tool to transfer over all the columns in our original Data Table, but
we apply a UniqueConcatenate aggregation to all the transfer columns. This will
create the distinct effect we are looking for.
When you are done, you can click ‘OK’ to close the Pivot
Data dialog and then ‘Finish’ to close the Add Data Table dialog.
When you are done, you will see a new data table added that
has just distinct row.
You can also add a Data Relation so that the two Data Tables
are related. This will allow users to mark rows in one of the data tables , and
also mark related rows in the second Data Table. This is shown below where the
user marked a row in the ‘Distinct’ Data Table, and then the related rows were
also marked in the ‘Original Data Table’
