4 Replies Latest reply on Nov 23, 2012 10:35 PM by Rod West

    using the MAX function in discoverer desktop

      Hi ,

      I need to get the max - date as there are multiple dates for one person. Can anyone tell me how to use it in discoverer desktop. When i select the MAX from the folder definition, it creates as a separate column and it displays all the rows for that person...

      fyi..i am working with the person extra information (EIT), trying to modify the existing report...as you know, in the EIT, for a person - (ex: visa type - will have multiple rows under details section, and each of it has multiple fields...issue date is one of the fields..

      any quick answers greatly appreciated..

        • 1. Re: using the MAX function in discoverer desktop
          Rod West

          If you have multiple columns from the child table, such as the EIT table then you will need to select MAX for all these columns.

          Also if you have included some MAX columns and some detail columns from a child table then you may need to remove all the detail columns so that you get one row per person, then add the child columns back with the MAX function to get one row per person.

          Rod West
          • 2. Re: using the MAX function in discoverer desktop
            Thanks Rod,

            I am kind of new to discoverer-desktop (about 6 months now).I donot have access to SQL..Can you pl. give me an example or the exact query/function Or steps so i can use at the earliest.

            The Person EIT has these things:
            Type = Visa details
            'Details' section = has multiple rows, and each row has extra info (visa type, visa#, issue_dt, exp_dt)...

            so, i need to get only the current row/data for the person.. How do we get that..How do we pick up the latest 'Detail' row..

            I tried using the MAX function on Issue_date (which i think is wrong right) from the 'Select Items' section (disco-desktop), from the Person Extra Informat Type folder....but, this does not work.. Then, i thought i need to write a subquery or a new condition.. to somehow pickup only the 'current row'..

            pl. let me know..

            • 3. Re: using the MAX function in discoverer desktop
              Hi Rod,

              Can you pl. respond to my earlier question below. If u can get me an example that would be great (condition/calculation/subquery?).. i can use it the same way ..

              thx, sree
              • 4. Re: using the MAX function in discoverer desktop
                Rod West

                To get the latest row you have two options. You can use an aggregate function ie. MAX so that you get one row for each person. Then use the MAX function to pick the value from the group of rows that have been aggregated together.

                So for the latest visa_type based on the issue_dt you would use a calculation like:

                max(visa_type) keep (dense_rank first order by issue_dt)

                The other option is to use an analytic calculation to order the rows for a person based on the issue_dt, something like:

                row_number() over (partition by person_id order by issue_dt)

                Then add a condition where this calculation = 1.

                Rod West