Boolean Problem

[Cass]

I have a DB with fields SN (auto entered), NameSN (used to relate to a Names DB), Year and additional fields. An extract might look like this:

SN NameSN Year

7 11 2002

8 14 2002

9 14 2003

10 21 2003

I need a find request that will return only records like SN7. Obviously, Find 2002 will return both records like SN7 and SN8. Thanks for any help.

[-Queue-]

Is that your only criteria? If so, then you can only do it by searching for "==7" in the SN field.

[Cass]

Guess I didn't make myself clear. To be specific, some NameSNs (i.e., some people) paid dues in 2002, some paid in 2003, and some paid in both 2002 and 2003. If the people who paid in 2002 are Set A and the people who paid in 2003 are Set B, what I want to find are the people who paid in 2002 and only in 2002. In other words, I want to eliminate the conjunction of Set A and Set B.

[Ugo DI LUCA]

Hi,

Sorry, still not clear for me. What about those who paid in 2002 and 2003. What set is it ?

My guess is that you would need a calculation return the year according to a SelfJoin on the NameSNs, kind of c_match = Max(SelfJoin::Year), or combine it with a Case statement to "eliminate" the conjunctions, as for example Case(Count(SelfJoin::Year)>1, Min(SelfJoin::Year), Max(SelfJoin::Year)).

There are lots of ways to do it. It also depends about how you wish to perform this search...

[Cass]

Hi Ugo,

I suspected solving this problem will involve a self join but I still don't understand the details. Guess I will have to study self joins.

About your last question: All the people who paid in 2002 are the set A. All the people who paid in 2003 are the set B. The people who paid in BOTH 2002 and 2003 are the conjunction of set A and set B (the "overlap" of the two circles of a Venn diagram). The people who paid only in 2002 are a subset of A and the people who paid only in 2003 are a subset of B. I want to find the subset of A that paid only in 2002 - so I can write them a dunning letter. The fields are: Check SN (unique, auto entered); Name SN (which relates to a Names database) and Year (for now the only entries are either 2002 OR 2003 - I just started keeping records) plus fields for the checks: date, amount, check number). Hope this helps in defining the problem.

Cass

[Ugo DI LUCA]

Hi,

A SelfJoin is simply a relationship made within the Same File, thus having your Unique 'Name SN' for both the Left and Right Side of the relationship would lead to a SJonName

If the data in your files only had these 2002 and 2003 years, this would be easy, maybe using the calcs given above. I suspect with the years, you'd also have to deal with 2004,2005,...and maybe even people who paid in 2002,2003,2004.

Another approach involving a related value list from the SelfJoin, would solve all problems.

1) Create the SJonName

2) Create a Related Value List of the 'Years' Field - YearsFromSJList

3) Create a calculation (unstored) c_Years = ValueListItems(Status(CurrentFileName),"YearsFromSJList")

This will lead you with a field holding all the years separated by a Carriage Return.

Here are 2 solutions which I can think about right now.

1.Solution 1 :

Use a global field (g_YearSearched) where you would enter the Year you're looking for, and a boolean Case calculation

c_Match = Case(Patterncount(c_Years,g_YearSearched)=1,Case(Patterncount(c_Years," ")>0,0,1),0)

The First part of this calc checks if the customer paid in the year you're looking for, while the second "eliminate" customers who paid in more than one year.

When c_Match equal 1, you'd get the subset you're looking for.

2. Solution 2 :

Use another calculation c_Years2 = Substitute(ValueListItems(Status(CurrentFileName),"YearsFromSJList")," ","")

and make your search for "==2002" in that field.

HTH

[Cass]

Hi Ugo,

Thanks so much for taking the time to address my problem. I really appreciate it. Now on to study your solution.

Cass