topic Load Distinct in QlikView

Load Distinct

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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‌

Re: Load Distinct

sunny_talwar — Wed, 31 Jan 2018 13:46:52 GMT

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

Re: Load Distinct

Anonymous — Wed, 31 Jan 2018 13:51:46 GMT

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.

Re: Load Distinct

sunny_talwar — Wed, 31 Jan 2018 14:03:22 GMT

Can you share your script?

Re: Load Distinct

stevedark — Wed, 31 Jan 2018 23:32:11 GMT

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