Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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.
Thank you.