Skip to main content
Announcements
Qlik Acquires Upsolver to Advance Iceberg Solutions: READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
johnw
Champion III

If I understood the question:

count({<target={'y'}>} accssNUmber)

View solution in original post

8 Replies
johnw
Champion III

If I understood the question:

count({<target={'y'}>} accssNUmber)

prieper
Master II

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

Not applicable
Author

Can you check the attached file? Is it what you were looking for?

johnw
Champion III

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.

Not applicable
Author

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

Not applicable
Author

i have jsut checked and all seems to be workign so far. Many thanks.

Dan

Not applicable
Author

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

johnw
Champion III

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.