Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Load Distinct

Hi all,

I'm having problems loading data into QV.

An example of my data is below.

NameIDScore
John SmithJS10
Jack SmallJS9
Paul KnowlesPK7
Gemma SmithGS9

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

gwassenaarhicstevedarkrwunderlichjimhalpertafderika

4 Replies
Highlighted

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

Highlighted
Creator
Creator

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.

Highlighted

Can you share your script?

Highlighted
MVP & Luminary
MVP & Luminary

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