Distribution Function

[Andrew Lister]

The recently posted Distribution function mostly solved the problem I described (in the wrong forum, I guess) here:

http://www.fmforums.com/forum/showtopic.php?tid/197082/

The Distribution function is here:

http://www.briandunning.com/cf/890

My one remaining question is how I can change the sort order of the output. Currently, the function outputs a list sorted alphanumerically by the order of uniqueList, I think. I would like the list sorted by the number of occurrences (of the members of uniqueList in dataList), most occurrences first. Is this hard to do? Any pointers would be much appreciated.

[comment]

Your first post did not describe your problem clearly enough (for me, at least). Opening a new thread doesn't make it any clearer, it only adds more confusion.

[Andrew Lister]

This is actually a new question, specifically about the sort order of the Distribution function, and how it might be tweaked. I was just referencing my other post for background - maybe that wasn't necessary. But I'll try again to describe the problem and then explain how the Distribution function mostly solves it.

I have a many-to-many relationship between Persons and Propositions. For any given proposition (call it P1), I want to know not only who asserted it, but also what other propositions people asserting P1 also asserted - and I would like the list of these associated propositions to be rank-ordered, with the most popular propositions coming first. I suppose the business analogy would be wanting to know, for people who bought product P1, what other products they tended to buy, most popular products first.

No doubt there is an easier way to do this, but here's what I came up with:

- define a calculation field in the Persons table that lists all of the propositions the person asserted.

- define a calculation field in the Propositions table that concatenates all of these lists, for those persons asserting the proposition in question

- use the Distribution function to count the occurrences of each proposition.

So for each proposition P1, I now have a field that contains a list of how many times each other proposition was asserted by those asserting P1. The only remaining problem is that this list is sorted by the ID number of the proposition; I would like it sorted by the number of occurrences. That's why I was posting here about the Distribution function.

I hope this is a better explanation of what I'm trying to do, and why I think the Distribution function is useful.

[Tim W]

Hi,

I think this is what you are looking to do.

I tweaked the orginal cf as well as running the list through a sort cf and a display formating cf.

Let me know what you think.

The sample file is attached.

HTH,

Tim

List_by_CountExample.zip

[bruceR]

Hi,

I think this is what you are looking to do.

I tweaked the orginal cf as well as running the list through a sort cf and a display formating cf.

Let me know what you think.

The sample file is attached.

HTH,

Tim

Looks good but what is your intended result for a value in uniqueList that has no match? Right now it doesn't work well.

Also, your example didn't handle multi-word values. See attached which handles multi words and handles no match.

List_by_CountExample.zip

Edited August 5, 2008 by Guest

[Tim W]

Looks good but what is your intended result for a value in uniqueList that has no match? Right now it doesn't work well.

Also, your example didn't handle multi-word values.

Thanks for the file.

A few tweaks to the existing functions can solve both of these issues. The modified file is attached.

However, I do like your tabbed output. It adds a little more complexity to the structure, but the result is nice.

Thanks again,

Tim

List_by_CountExample.zip

[Andrew Lister]

Thanks very much, Tim and Bruce, these functions do exactly what I was wanting to do - very useful.

[bruceR]

Thanks for the file.

A few tweaks to the existing functions can solve both of these issues. The modified file is attached.

However, I do like your tabbed output. It adds a little more complexity to the structure, but the result is nice.

Thanks again,

Tim

Actually, I don't think any additional complexity was added by using the tab except for adding the tab character.

The file did demonstrate some additional techniques that are not necessary to the basic function; such as the ID accumulator.

[Tim W]

Hi Bruce,

I must have missed something then.

When I tried adding the tab to the output line, it bounced the count to different locations. Then I tried to do the math to adj for the difference in the length of the items, same result only adjusted somewhat. So, from those attempts I assumed it was being adjusted somewhere else. But, however you used it, it lined up nicely.

What's the trick?

Thanks,

Tim

[bruceR]

Hi Bruce,

I must have missed something then.

When I tried adding the tab to the output line, it bounced the count to different locations. Then I tried to do the math to adj for the difference in the length of the items, same result only adjusted somewhat. So, from those attempts I assumed it was being adjusted somewhere else. But, however you used it, it lined up nicely.

What's the trick?

Thanks,

Tim

The trick is to keep the tab in the line but set the tab stops in the text format of the field. See attached.