Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Are you doing a join on ID field later on in the script?
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.
Can you share your script?
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