topic Re: Count on Linked field problem in QlikView

Count on Linked field problem

Thu, 19 May 2011 19:44:12 GMT

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

Re: Count on Linked field problem

johnw — Thu, 19 May 2011 19:49:56 GMT

If I understood the question:

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

Count on Linked field problem

prieper — Thu, 19 May 2011 19:52:27 GMT

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

Re: Count on Linked field problem

Thu, 19 May 2011 19:53:06 GMT

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

Re: Count on Linked field problem

johnw — Thu, 19 May 2011 20:01:55 GMT

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.

Re: Count on Linked field problem

Thu, 19 May 2011 20:55:34 GMT

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

Re: Count on Linked field problem

Fri, 20 May 2011 10:18:16 GMT

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

Dan

Re: Count on Linked field problem

Fri, 20 May 2011 11:20:09 GMT

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

Re: Count on Linked field problem

johnw — Fri, 20 May 2011 17:18:19 GMT

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.