Load Distinct

[Anonymous]

Hi all,

I'm having problems loading data into QV.

An example of my data is below.

Name	ID	Score
John Smith	JS	10
Jack Small	JS	9
Paul Knowles	PK	7
Gemma Smith	GS	9

I'm trying to do a distinct load but when I count the number of records it is doubling where there is two of the same ID's.

I've no idea why and I can't fix it.

Has anyone seen this before?

Thanks

Gareth

gwassenaar‌hic‌stevedark‌rwunderlich‌jimhalpert‌afd‌erika‌

[sunny_talwar]

Are you doing a join on ID field later on in the script?

[Anonymous]

Yes Sunny, but I've commented out that part of the script and re run it and still get the same result.

The count should be 4 but I'm getting 6 as it is double counting on ID.

[sunny_talwar]

Can you share your script?

[stevedark]

Hi Gareth,

The rows are not distinct.  For a distinct to remove a row all values must be identical - including the numbers.

What you may want is a group by in the load script, something like this:

Table:

LOAD

  ID,

  CONCAT(DISTINCT Name, ', ') as Names,

  SUM(Score) as Score

FROM YourDataSource.qvd (qvd)

GROUP BY ID

;

However, in QlikView (and Sense) you don't need to do this.  Simply load the table without doing a distinct and then create a table with ID as a dimension and Sum(Score) as the expression and you will get three rows with a Score of 19 for the ID JS.

Looking at the data though... it looks like the two JS IDs are actually two different individuals, so probably should be reported separately.

Or maybe I'm missing the point, and what you need to do is simply replace a COUNT(ID) statement with COUNT(DISTINCT ID) in the front end.

Steve