- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count on Linked field problem
Hi All
I am trying to get a total for the number of records in the main table where it is a 'y' value in a linked table. It seems to just be counting the number of 'y' values in the linked table not the number of records in the main table that are linked to a 'y'. See varDenominator in example attached,.
Any suggestions would be welcomed.
Thanks
Dan
- Tags:
- count
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I understood the question:
count({<target={'y'}>} accssNUmber)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You need to count a unique field in the table, if this does not exist, duplicate the keyfield and count the number of this duplicate, approximately as fllws:
LOAD
Key,
Key AS KeyForThisTable
....
Now you need to count KeyForThisTable.
HTH
Peter
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you check the attached file? Is it what you were looking for?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
prieper wrote:
You need to count a unique field in the table, if this does not exist, duplicate the keyfield and count the number of this duplicate
A straight count(field) will count every occurrence, unique ID or not. The difficulty comes when you count a linking field appearing on more than one table, such as "Order" in this case. I would typically then do a count(distinct) to get a proper count, but THAT would only return a row count if the field was a unique ID, which it is not in this case, which may have prompted your comment. In this case, though, accssNUmber was only on the table we wanted to count the rows of, so served the purpose just fine.
I may, of course, have misunderstood the question. It seems like we all interpreted the question differently.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much everyone for your very fast help. John i think your first answer should do the trick. I need to go into work tommorow morning (am in london) and test it. Thanks again and i will let you know how it goes tommorow.
dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i have jsut checked and all seems to be workign so far. Many thanks.
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi John,
For my future reference is there anyway i could have found the answer to my problem in the help or user manuals? or something i should have been searching for?
Thanks again
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm really not sure exactly how you could find the answer in the help text or reference manual. I used set analysis, and there's a section on set analysis, but it's not going to tell you exactly what to do, though I suppose it's fairly clear once you have a good grasp of set analysis. But probably nothing's going to tell you "you should use set analysis for this case" except for experience.
And of course there's some explanation of count(), but it gives no indication of any potentially unexpected behavior when counting a linking field, or other ways of approaching the count in that case (such as counting a different field on the table, making a copy of the field you want to count on the table you want to count it on, or making a counter on the table you want to count it on and doing a sum() of the counter).
Looks like count(if(target='y',accssNUmber)) works as well, though that's less efficient than set analysis.