2 Replies Latest reply: Feb 18, 2016 12:01 PM by Oleg Troyansky RSS

    Earliest Value

    reddy r

      T1:

         

      IDNameDateText
      100xxx10/1/2015test1
      100xxx10/1/2015test2
      200yyy10/2/2015test5
      200yyy10/2/2015test6
      300zzz10/3/2015test7
      300zzz10/3/2015test8
      300zzz10/3/2015test9

       

      in the above table

       

      in Text field test1 is the earliest value of ID 100,

      test5 is the earliest value of ID 200,
      test7 is the earliest value of ID 300,

       

      we need to show earliest values for the above IDs as a reult.:

       

       

      Could u pls help me to get the below result:

      Result:

         

      IDNameDateText
      100xxx10/1/2015test1
      200yyy10/2/2015test5
      300zzz10/3/2015test7
        • Re: Earliest Value
          Stefan Wühl

          How do you determine earliest value? Your Date values are the same per ID.

           

          If you are referring to the first record in your table per ID, try

           

          NOCONCATENATE

          LOAD

               ID, FirstValue(Name) as Name, FirstValue(Date) as Date, FirstValue(Text) as Text

          RESIDENT YourTable

          GROUP BY ID;

           

          DROP TABLE YourTable;

          • Re: Earliest Value
            Oleg Troyansky

            It looks like you need to aggregate your data with the GROUP BY load. SOmething like this:

             

            LOAD

                 ID,

                 Name,

                 Date,

                 FirstSortedValue(Text)

            RESIDENT

                 DetailedTable

            GROUP BY

                 ID,

                 Name,

                 Date

            ;

             

            You may have to sort your data in the desired order before grouping, or play with the optional sort-weight parameter of the FirstSortedValue() function.

             

            cheers,

            Oleg Troyansky

            Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!