7 Replies Latest reply: Apr 12, 2016 8:03 AM by xavier hemelaar RSS

    MIN and Group By

    xavier hemelaar

      Hi Qlikers,

      I would like to know how I can fin the Responsible for the MIN(creation date) and Type=A

      Table is (result in yellow)

           

      AncestorIDTypecreation date responsibleMin dateMin Responsible
      1A11/04/2016Ben11/04/2016Ben
      1A12/04/2016Tom11/04/2016Ben
      1B13/04/2016Jim
      1A14/04/2016Ben11/04/2016Ben
      1C15/04/2016Jim
      1C16/04/2016Tom

      load
      AncestorID,

       

      MIN(IF([Type]='A',[creation date])) AS [Min date],

      ..... AS  [Min Responsible]

      Resident test  Group By AncestorID ;

       

      Thanks for your help

        • Re: MIN and Group By
          Sunny Talwar

          Try this:

           

          Table:

          LOAD AncestorID,

               Type,

               [creation date],

               responsible

          FROM

          [https://community.qlik.com/thread/212749]

          (html, codepage is 1252, embedded labels, table is @1);

           

          Left Join (Table)

          LOAD Type,

            Date(Min([creation date])) as [Min Date],

            FirstSortedValue(responsible, [creation date]) as [Min Responsible]

          Resident Table

          Where Type = 'A'

          Group By Type;


          Capture.PNG

            • Re: MIN and Group By
              kushal chawda

              slight modification. Add AncestorID as well in group by

               

              Table:

              LOAD AncestorID,

                  Type,

                  [creation date],

                  responsible

              FROM

              [https://community.qlik.com/thread/212749]

              (html, codepage is 1252, embedded labels, table is @1);

               

              Left Join (Table)

              LOAD AncestorID,

                        Type,

                Date(Min([creation date])) as [Min Date],

                FirstSortedValue(responsible, [creation date]) as [Min Responsible]

              Resident Table

              Where Type = 'A'

              Group By AncestorID,Type;


                • Re: MIN and Group By
                  Sunny Talwar

                  And in the load?

                   

                  Left Join (Table)

                  LOAD AncestorID,

                    Type,

                    Date(Min([creation date])) as [Min Date],

                    FirstSortedValue(responsible, [creation date]) as [Min Responsible]

                  Resident Table

                  Where Type = 'A'

                  Group By AncestorID,Type;

                    • Re: MIN and Group By
                      kushal chawda

                      Just added

                      • Re: MIN and Group By
                        xavier hemelaar

                        Hi,

                         

                        Sorry for my late reply. But I was trying to make this solution work with my data. So the solution you gave me works perfectly fine as long as there are no double (Min([creation date]) . In my case, most of the time, I will have several Min([creation date]) for a same AncestorID.

                         

                        So what Qlik does, is to leave [Min Responsible] blank because several solutions are possible. So I was trying to use Concat to have my [Min Responsible] with all the possible values.

                         

                        Or, other option, is to force Qlik to pick the first value possible. So, if I have several responsible at a same (Min([creation date]), I want to pick at least one value (doesn't matter wich one).

                         

                        But for now it doesn't work yet...