3 Replies Latest reply on Feb 9, 2014 7:56 PM by Aaron Clancy

    Corresponding values from maximum in the previus column

    piero giansanti

      Hi all,

       

      I am currently working with a data set like this

       

      UID000110sold
      UID000112wait
      UID00025wait
      UID000355sold
      UID00031wait
      UID00048sold

       

      What I would like to do is to aggregate the data on column 1 and take the corresponding maximum  in column 2.

      UID000112
      UID00025
      UID000355
      UID00048

       

      This is easy, ok! However, at the same time, I would like to obtain from column 3 the value corresponding to the max in column 2, that is

       

      UID000112wait
      UID00025wait
      UID000355sold
      UID00048sold


      How can I do this?

       

      Thank you in advance for your help.

        • 1. Re: Corresponding values from maximum in the previus column
          Aaron Clancy

          In order for this to work I added a unique identifier to your data (which I would recommend as a good best practice for many data storage scenarios)

          Screen Shot 2014-02-08 at 11.09.40 AM.png

          Then I created a filter calc:

          Screen Shot 2014-02-08 at 11.10.37 AM.png

           

          I set up the columns and rows like the following:

          Screen Shot 2014-02-08 at 11.11.26 AM.png

          (All Discrete fields)

           

          Then I placed the filter on the filters shelf with the following compute using configuration:

          Screen Shot 2014-02-08 at 11.12.29 AM.png

          Screen Shot 2014-02-08 at 11.13.09 AM.png

           

          Set the filter to "Yes"

          Then uncheck "Show Header" for the uniqueID field.

           

          The result should be:

          Screen Shot 2014-02-08 at 11.13.43 AM.png

          • 2. Re: Corresponding values from maximum in the previus column
            Joshua Milligan

            Peiro,

             

            Aaron's approach definitely works.  Here's a variation which avoids adding a unique identifier (although I agree with Aaron that it is often a good practice).


            Create a single calculated field that evaluates whether the SUM of the value is the same as the WINDOW_MAX.  This is very similar to Aaron's first step.

            0.png

             

            When you place it in the view like this, and set it to comput using Type (right click [Is Highest] on Rows and select "Compute Using" --> "Type"), then you'll get a True value for each aggregate row you want to keep.


            1.png

             

            Simply drag the [Is Highest] field from Rows to Filters and keep the True values.  This is the result:

             

            0.png

             

            Regards,

            Joshua

            • 3. Re: Corresponding values from maximum in the previus column
              Aaron Clancy

              I agree with the approach.

              The only caveat I would include is that:

              If an ID can have multiple instances of the same "type" then they're going to get rolled in together as opposed to individual records.

              If this never happens in the data then it doesn't matter.

              On that note, if it never happens then ID+TYPE = composite unique ID and could be considered a unique identifier that already exists in the data.

               

              Sent from my iPhone