15 Replies Latest reply: Nov 12, 2011 1:47 PM by Robert Hutchings RSS

    Select first entry found from a table and ignore the rest

    Robert Hutchings

      I can do this is excel first (using vlookup) and then just load table A (with the table B date)  into QlikView.


      But I assume thsi can be easily done in QlikView but I can not see how


      What I want to do is only select the first record from  table B


      So table A


      Call ---Serial number

      1  ---------123

      2 ----------124


      Table B


      Serial No--- Date purchaseD

      123  -----------1/01/2003

      123 ------------1/02/2003 (entered in error)

      124   -----------1/3/2003


      Serial number 123 has been entered twice in error in table B. I only want one date not two in the combined table


      Thanks for any help on this.


      (I was hoping their would be something like a left join one record only)

        • Select first entry found from a table and ignore the rest
          Stefan Wühl



          I assume the correct Date purchase is the one with minimum date, right?


          You could indeed use a left join, maybe like this:



          LOAD * INLINE [

          Call, Serial number







          LOAD * INLINE [

          Serial number, Date purchaseD






          left join (TableA) LOAD

          [Serial number],

          Date(min([Date purchaseD])) as minDate

          resident TableB group by [Serial number];


          So I grouped the TableB records by serial number and only retrieved the min date, then joined this one record to Table A.


          Alternatively, you could use similar in the frontend, leaving TableA and TableB as read in raw:

          If you created a table chart with Call and Serial number as dimensions, use something like


          Date(aggr( min([Date purchaseD]),Call,[Serial number]))


          as expression.


          Hope this helps,