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

          RJ,

           

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

           

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

           

          TableA:

          LOAD * INLINE [

          Call, Serial number

          1,123

          2,124

          ];

           

           

          TableB:

          LOAD * INLINE [

          Serial number, Date purchaseD

          123,01/01/2003

          123,01/02/2003

          124,01/03/2003

          ];

           

          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,

          Stefan