select expression evaluated on key column returns duplicates

[Majeed]

Hello, 

I have a table named MEDECINS whose key column named ID MEDECIN only contains unique id values. 

I have an expression that looks like this : 

COUNT(SELECT(MEDECINS[ID MEDECIN], long select-row expression)

Even though the MEDECINS[ID MEDECIN] column contains uniqueid values (it is the key column), 

i get two different values depending on if i add ,TRUE as a [ , distinct-only? ] parameter in my select () expression or not. I would like to understand why I get two different values depending on if i add ,TRUE or not. 

In my mind it shouldn't make a difference because there are no duplicate values in the ID MEDECIN column that is being evaluated.

Thank you very much in advance for your lightings. 

Best regards, 

[graham_howe]

So what happens with the following two expressions?

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE))

and 

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE, TRUE))

Do they give different answers?

[Majeed]

Hello Graham. 

My expression: 

COUNT(SELECT(MEDECINS[ID MEDECIN],  select-row conditions)

and

COUNT(SELECT(MEDECINS[ID MEDECIN],  select-row conditions, TRUE)

give different answers. 

I am trying to firgure out why it gives different answers. 

In my mind it should not  give different answers because there are no duplicate values in the ID MEDECIN column that is being evaluated.

When I omit ',TRUE' (as a  [ , distinct-only? ] parameter), some values are returned more than once. 

[Joseph_Seddik]

Hello @Majeed,

Your reply is exactly the same as the original post. Would you please instead perform the test that @graham_howe suggested and post screenshots of the results? Thanks in advance.

---

@graham_howe wrote:

So what happens with the following two expressions?

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE))

and 

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE, TRUE))

Do they give different answers?

---

[Majeed]

Thank you @Former Community Member and   @Joseph_Seddik for your messages. 

Here are the two full expressions and screenshots of their respective resuts

WITHOUT ",TRUE": 

COUNT(SELECT(MEDECINS[ID MEDECIN],
AND(
[ID CIRCUIT].[NOM COMPLET VISITEUR]=[_THISROW].[NOM COMPLET VISITEUR],
[STATUT]<>Interne, 
IN([ID MEDECIN],SELECT(VISITE MEDICALE[ID MEDECIN], 
AND(
[NOM COMPLET VISITEUR]=[_THISROW].[NOM COMPLET VISITEUR],
YEAR([DATE ET HEURE CHECK IN])=[_THISROW].[ANNEE],
MONTH([DATE ET HEURE CHECK IN])=[_THISROW].[MOIS]
)
))
)
))

RESULT: 

WITH ",TRUE": 

COUNT(SELECT(MEDECINS[ID MEDECIN],
AND(
[ID CIRCUIT].[NOM COMPLET VISITEUR]=[_THISROW].[NOM COMPLET VISITEUR],
[STATUT]<>Interne, 
IN([ID MEDECIN],SELECT(VISITE MEDICALE[ID MEDECIN], 
AND(
[NOM COMPLET VISITEUR]=[_THISROW].[NOM COMPLET VISITEUR],
YEAR([DATE ET HEURE CHECK IN])=[_THISROW].[ANNEE],
MONTH([DATE ET HEURE CHECK IN])=[_THISROW].[MOIS]
)
))
)
,TRUE))

RESULT: 

[Joseph_Seddik]

@Majeed 

Would you please just test with 

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE)) 

and 

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE, TRUE))

and show the results?

Thank you.. 

[Majeed]

Dear @Joseph_Seddik 

I just tested with 

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE)) 

and 

COUNT(SELECT(MEDECINS[ID MEDECIN], TRUE, TRUE))

The two expressions return the exact same result, below:

[Joseph_Seddik · Accepted solution]

I don't know, I'll suggest some ways to troubleshoot:

1. Your expressions returns 69 and 45 rows respectively, not that much, so try to look at the results, compare them and see what rows where omitted and see what's particular about them. 
   
   
2. Replace SELECT by FILTER (which more appropriate in your case) and combine with UNIQUE()
   
   
3. Instead of the third SELECT argument, use UNIQUE()

[Steve]

I suggest you review your spreadsheet. I suspect there are, in fact, duplicate key column values.

[SkrOYC]

Also, just to add a litle to this conversation, Select(Table[Key]) is not actually the best tool for the job.

There is Filter() with that.

If Filter() returns the same problem, your worksheet is the problem.

BTW, I don't know if blank values are considered, but maybe you deleted rows and they are considered the same key (blank)

FILTER() | AppSheet Help Center

[Majeed]

Thank you all for your answers and support.