1 Reply Latest reply: Mar 8, 2011 5:21 PM by Nicolas Stefaniuk RSS

    Duplicate Values Problem - Data is accurate but only want to select first result

      Hi,

      I am new to Qlikview and have been getting some problems in trying to manage some invoices which we are pulling out of a database to analyse.

      My problem is that we are pulling through invoices that are unique and valid from our database.

      The Issue is:-

      • invoices are duplicated as the order can be split up into segments if not all the order is in stock. Therefore our values are inaccurate by however many segments the order is broken down into. (We can't count number of records and divide by that as segments can be different sizes and have a moving exchange rate)

      The Solution I am looking for is:-

      • I would only like to select the first value from this result (and ignore the rest) but I am unsure how to do this.

       

      The fields that are the same are Invoice Number, Invoice Item and Billing date, the field that changes is the condition record.

      I am not sure if there is a way in Set Analysis to filter these out, or if there is a way to stop this in the script itelf.

      Thanks for any help recieved.

        • Duplicate Values Problem - Data is accurate but only want to select first result
          Nicolas Stefaniuk

          Why don't you add a distinct / group by clause in the SQL query at the loading?

           

          Select
          distinct
          Invoice Number, Invoice Item, Billing date
          from table


          or

           

          Select

          Invoice Number, Invoice Item, Billing date
          // add some min / max clause to merge all the lines into 1
          from table
          group by Invoice Number, Invoice Item, Billing date


          or in Oracle to get only the first line of each Invoice Number, Invoice Item, Billing date (assuming that a field "line_number" allows you to find which is the first

           

           

          Select
          Invoice Number, Invoice Item, Billing date
          ,min(<attribute1>) keep (dense_rank first order by "line_number") "ATTRIBUTE1_FIRST_LINE"
          ,max(<attribute2>) keep (dense_rank first order by "line_number") "ATTRIBUTE2_FIRST_LINE"
          from table
          group by Invoice Number, Invoice Item, Billing date