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

Tue, 08 Mar 2011 11:05:30 GMT

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

nstefaniuk — Tue, 08 Mar 2011 22:21:27 GMT

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

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

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

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

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