1 Reply Latest reply on Oct 28, 2016 1:26 AM by Nikola Stojanovic

    Filtering out repeating (blank) rows in a table

    Nikola Stojanovic

      I am trying to create a basic text table that lists the value(s) of several dimensions that are associated with a person and display the results of other calculated fields that meet certain conditions (e.g. IF ( [Person Type] = 'SPOKESPERSON') THEN 'Y' ELSE ' '  END) in a single row. The calculated fields seem to be returning the correct results but I cannot achieve the formatting that I need. The "Y" values display on different rows within the context of the person/proposal. I assume that this is happening because the person may have anywhere from 1-3 roles, but I am just trying to mark if they meet one of the roles per column while maintaining that information on one row. And looking at the underlying data produces 40+ rows of seemingly redundant data. I highlighted the rows in light blue. I have been messing around with LOD Calculations trying to exclude values but keep running into issue because they are text strings and AGG mismatches.

       

      So the goal is to have one line/record per "Proposal No" that either has a "Y" or blank value for the corresponding columns. Any leads would be appreciated. Also, let me know if you think a sampling of the data would help to resolve this issue.

       

      Person Info copy.jpg

        • 1. Re: Filtering out repeating (blank) rows in a table
          Nikola Stojanovic

          Stanford University

           

          So I think I came up with a solution to the problem and I now have the Proposal History table view/crosstab formatted the way I/our management wants it, straight across with no duplicate values or empty cells. The issue was around the [Proposal Person Type] dimension that contains up to 4 values/members. So when I was dropping my original calculated fields into the Viz I was able to obscure or hide the values that I didn't want for a certain condition (e.g. IF [Proposal Person Type]  = 'SPOKESPERSON' THEN 'Y' ELSE '' END). But I was not really removing them from the Viz LOD so I was getting multiple rows and duplicating rendering on my last column because under certain conditions a person might have 2-3 roles/values.

           

          I ended up creating 2 new calculated fields that force a return of one value. In this case I am lucky that the two [Proposal Person Type] roles that I am interested reside at the min and max positions. It's late and I'm not sure if this explanation is making any sense. This doesn't feel like the most elegant solution to the problem so if anyone has any other ideas I would be interested in hearing them.

           

          Calculated Field for Spokesperson

          IF MAX([Proposal Person Type])  = 'SPOKESPERSON' THEN 'Y' ELSE '' END

           

          Calculated Field for Collaborator

          IF MIN([Proposal Person Type]) = 'COLLABORATOR' THEN 'Y'

           

          All Proposals.jpg