DISTINCT vs. DISTINCTROW

Anonymous — Tue, 27 Aug 2013 21:28:32 GMT

Hello to all. I have a query in Access that I am trying to recreate in QlikView. The first part of the query in Access is written as SELECT DISTINCTROW. I have found no way of replicating SELECT DISTINCTROW in QlikView. There is not way (that I know of) to SELECT DISTINCTROW in a LOAD statement. I have tried simply SELECT DISTINCT, but do not get the same amount of records when using this method. Has anyone ran into a similar problem? Is there a way to SELECT DISTINCTROW? Thank you.

Re: DISTINCT vs. DISTINCTROW

rajeshvaswani77 — Wed, 28 Aug 2013 04:01:58 GMT

Hi,

Below is the definition of DistintRow in access

Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:

SELECT DISTINCTROW CompanyName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName;

If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

"Taken from Microsoft Access Help"

In QlikView you would have to use Firstsortedvalue to achieve this, as you need only one record.

thanks,

Rajesh Vaswani

topic Re: DISTINCT vs. DISTINCTROW in QlikView

DISTINCT vs. DISTINCTROW

Re: DISTINCT vs. DISTINCTROW