difference between the count() and actual rows in the table

[su_pyae]

Hello everyone, 

I am stumped. I am trying to get data from AWS.

The data has a column named ID which I will use later to get other data by using Left Join. 

There are duplicated values in the ID column. 

When I use ID and Count(ID), it will show me something ID = 1 and Count(ID) = 32. 

But the table only has like 3 rows. 

I would like to know why I am having the difference between the count() and actual rows in the table. 

Thank you so much. 

[albertovarela]

A few suggestions:

-Check that field ID is not a key to other table in the model. If so, you are counting all values in the model for that field 

-If you have ways to perform a count at the source would help verifying you are not generating dupes while loading the data to Qlik.

- Add a RecNo  during the load your data.  That will add an integer for the read row during the load which you can treat as a Perfect key. Then you that for each ID

I hope this helps

[su_pyae]

1. I checked it and ID doesn't seem to be the key for other tables in the model. 

2. I don't have access to actual data in AWS. I am just using it via Qlik Sense. 

3. I added RecNo() but I am not sure how to use that. 

Let's say that I have 300 distinct IDs but I have 450 rows because some of the IDs are duplicated due to having different values in other columns. 

But when I use Left Join using ID, the number of rows almost doubled. Normally, you would expect to have 450 rows since you are using Left Join(). However, I keep getting bigger number of rows. 

[stevejoyce]

No, if you join a field with duplicate values it's going to multiply those rows.

so if you have 2 tables and join on ID, 1 table has 5 rows for ID = 100 and other table has 2 rows for ID = 100, you will get 10 rows for ID = 100.

[su_pyae]

Thank you.