Remove Repeating Values in a Table Visualization

[JRParker]

So we have a basic Table visual with two fields from two tables related via one-to-many relationship.  How does one prevent the repeating value on the 'one' side while repeating the values on the 'many' side:

Desired result:

Thanks in advance; presumably straight forward but apparently not for a rookie 🙂 

[parry2k · Accepted solution]

@JRParker use matrix visual, add lead_name and activity_note on rows, click drill down button, on format pane, turn off stepped layout and you will have the view you are looking for.

[parry2k · Accepted solution]

@JRParker use matrix visual, add lead_name and activity_note on rows, click drill down button, on format pane, turn off stepped layout and you will have the view you are looking for.

[JRParker]

Couldn't get this to work in the following scenario when the 'many' side has two fields (Note, Activity)

Lead                Note                        Activity

Lead 1	Note 1.1	Phone Call
Lead 1	Note 1.2	Phone Call
Lead 1	Note 1.3	Phone Call
Lead 2	Note 2.1	Meeting
Lead 2	Note 2.1	Phone Call
Lead 3	Note 3.1	Phone Call
Lead 3	Note 3.2	Meeting
Lead 3	Note 3.3	Meeting
Lead 3	Note 3.4	Meeting

Desired outcome:

Lead                Note                        Activity

Lead 1	Note 1.1	Phone Call
	Note 1.2	Phone Call
	Note 1.3	Phone Call
Lead 2	Note 2.1	Meeting
	Note 2.1	Phone Call
Lead 3	Note 3.1	Phone Call
	Note 3.2	Meeting
	Note 3.3	Meeting
	Note 3.4	Meeting

Thanks again parry2k!

[parry2k · Accepted solution]

@JRParker it will work, make sure you are using matrix visual, not table visual

[mjmowle]

Colleagues, 

The solution provided does work, but there is one problem with it:  The Matrix View does not appear to allow Horizontal scrolling.  In my situation, there are more columns to display than there is space on the page.  With a table, I can scroll to the right.  In a matrix I cannot.

Am I missing a setting that does allow Horizontal scrolling in the Matrix View, or is there now some other way to use the Table Visual and remove repeating information?

[JRParker]

thank you parry2k!   ... needed to familiarize myself with drill down feature of a matrix.

So now that there is a flattened table due to the Left Outer join (Merged within a Query), how does one limit the number on the many side of the table? Specifically, how does one limit the number of Activities for a given Lead to some # (say 2) (sorted by date in descending order.. there is a date field; just not show in the below matrix)?  

 

Thinking the # of Activities should be limited before the two tables are joined/merged.  The Activities table (the many table) is related to the Lead table via Lead-ID; each Activity record is uniquely identified by the Activity-ID, but there are multiple records with the same Lead-ID.  How would one list the Activities for a given Lead-ID in descending order by Activity-Date, and limit the number of Activity-IDs?

  In an Activities query, one could duplicate the Lead-ID and the Date fields, merge the two duplicated columns, and sort the merged column.  So one would have the list in descending order, but what isn't clear, assuming this is even the correct path to take, is how to limit the number of Activities for a given Lead.