Lookup function based on two columns

[Martin-24]

Hello


My data looks like this

WRESTLER   TOURNAMENT   RANK   OPPONENT   OPPONENT_RANK

John            5         1       Bill
John            5         1       David
John            6         3       George
Bill            5         2       David
George          6         4       John
...and so on...

(RANK corresponds to WRESTLER)


Now, I need to find out the rank of the opponent (OPPONENT_RANK). I want to
look up OPPONENT and TOURNAMENT in the first two columns, and the resulting
value in OPPONENT_RANK should be RANK (from the row where the 2 criteria are
met).

For example, the first entry in OPPONENT_RANK would be 2 as "Bill" and "5"
can both be found in row 4 where RANK="2". The sample table should look like
this at the end:

WRESTLER   TOURNAMENT   RANK   OPPONENT   OPPONENT_RANK

John            5         1       Bill          2
John            5         1       David        N/A
John            6         3       George       N/A
Bill            5         2       David        N/A
George          6         4       John          4
...and so on...

(RANK corresponds to WRESTLER)


Sorry, it is quite difficult to explain. Furthermore,
- each wrestler has several bouts per tournament
- each wrestler has only one ranking per tournament
- the wrestler cannot have the same opponent twice in a tournament
- in Excel it would be a normal VLookup-Function but with 2 criteria (I
managed to do it in Excel but it would take days! to calculate it since I
have a large number of bouts).


Anyone who can solve this problem is my hero! Thank you in advance!

Martin

[Martin-24]

Don't worry, I was able to do it in Excel. It is not a very nice solution,
however, it works and I am able to export it to SPSS.

[Maguin, Eugene]

Martin,

Heroes need help. And in this case, lots of it. Providing the example that
you did is a great start. But a lot remains. I understand the opponent_rank
value for Bill. But let's talk about John. Why should he have an
opponent_rank value of 4? When you look up John, you see that he had two
bouts (Bill, ranked 2, and David, unranked) in tournament 5 and one bout in
tournament 6 (George). Now, George is a problem also but I come back to him.
If you want a solution to this, you have to provide a consistent rule for
deciding which tournament-bout combination is going to be used when a person
has multiple bouts. Now back to George: George has a rank of 4. Why then
does George have an opponent_rank value of N/A? I think I should be 4.

WRESTLER   TOURNAMENT   RANK   OPPONENT   OPPONENT_RANK
John            5         1       Bill          2
John            5         1       David        N/A
John            6         3       George       N/A
Bill            5         2       David        N/A
George          6         4       John          4

By the way, explain what you mean by 'rank corresponds to wrestler'. If this
is true, how can John, apparently the same person, have ranks of 1 and 3?

Sorry, it is quite difficult to explain.

It may be but you will have to explain it.

Furthermore,
- each wrestler has several bouts per tournament

This would seem to indicate that you need a new variable called 'bout'.
True?

- the wrestler cannot have the same opponent twice in a tournament.

So what does this mean? How does it affect the solution you are asking us to
help you with?

In theory, this isn't very hard. If you add a bout variable, then the
combination of name, tournament, bout defines a unique value of rank.
Combine that change with a rule for selecting the rank to be retained when a
person has multiple tournaments and bouts and that gets rid of multiple
cases for a given name. Then sort by Wrestler, save that dataset with a NEW
NAME and in the save command, rename Wrestler to Opponent. Then, reopen your
original dataset, sort by Opponent, and match files the saved dateset to
this one just opened by Opponent. That may be a bit confusing but that's the
idea but before you get there, there are some things to make clear.

Gene Maguin

[Richard Ristow]

If I may weigh in (appropriate, for wrestling),
at 11:49 AM 5/15/2007, Martin wrote:

>My data looks like this
>
>WRESTLER   TOURNAMENT   RANK   OPPONENT   OPPONENT_RANK
>
>John            5         1       Bill
>John            5         1       David
>John            6         3       George
>Bill            5         2       David
>George          6         4       John
>...and so on...
>
>(RANK corresponds to WRESTLER)
>
>Now, I need to find out the rank of the opponent (OPPONENT_RANK).

Now, I take it that a wrestler's rank is fixed IN ANY ONE TOURNAMENT,
but can vary between tournaments. So you look up opponent by name and
tournament, not just by name.

It's interesting, because you're merging a file with itself. The
natural way to do it is to make a separate copy, and merge the two
copies. This is SPSS 15 draft output:

*  ................................................................. .
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |15-MAY-2007 17:03:30       |
|-----------------------------|---------------------------|
[Wrestlers]

WRESTLER TOURNAMENT RANK OPPONENT

Bill          5        2 David
George        6        4 John
John          5        1 Bill
John          5        1 David
John          6        3 George

Number of cases read:  5    Number of cases listed:  5


*  .....  Create a separate file, with each wrestler in the role of  .
*  .....  an opponent.                                               .
*  (Use ADD FILES, to have access to KEEP and RENAME subcommands.)   .
ADD FILES
    /FILE  =Wrestlers
    /RENAME=(Wrestler = Opponent)
            (Rank     = Opponent_Rank)
            (Opponent = Drop_This)
    /KEEP  = Opponent Tournament Opponent_Rank.

*  To get one record, only, per wrestler as an Opponent in each      .
*  tournament.                                                       .
*  (Rank should be the same in all a wrestler's records in one       .
*  tournament. This code doesn't check that.)                        .

AGGREGATE OUTFILE=*
    /BREAK = Opponent Tournament
    /Opponent_Rank = FIRST(Opponent_Rank).

DATASET NAME  Opponents.

LIST.

List
|-----------------------------|---------------------------|
|Output Created               |15-MAY-2007 17:03:31       |
|-----------------------------|---------------------------|
[Opponents]

Opponent TOURNAMENT Opponent_Rank

Bill          5            2
George        6            4
John          5            1
John          6            3

Number of cases read:  4    Number of cases listed:  4


*  .....  Sort Wrestlers by opponent, add opponent ranks using       .
*  .....  MATCH FILES, re-sort, and list.                            .

DATASET ACTIVATE Wrestlers WINDOW=FRONT.

SORT CASES BY Opponent Tournament.
MATCH FILES
    /FILE =*
    /TABLE=Opponents
    /BY    Opponent Tournament.

SORT    CASES BY Wrestler Tournament.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |15-MAY-2007 17:03:31       |
|-----------------------------|---------------------------|
[Wrestlers]

WRESTLER TOURNAMENT RANK OPPONENT Opponent_Rank

Bill          5        2 David           .
George        6        4 John            3
John          5        1 Bill            2
John          5        1 David           .
John          6        3 George          4

Number of cases read:  5    Number of cases listed:  5

===================
APPENDIX: Test data
===================
*  ................................................................. .
*  .................   Test data               ..................... .
DATA LIST LIST SKIP=1
    /WRESTLER(A8) TOURNAMENT(F2) RANK(F3) OPPONENT(A8).
BEGIN DATA
     WRESTLER    TOURNAMENT      RANK     OPPONENT   OPPONENT_RANK
     John             5            1       Bill
     John             5            1       David
     John             6            3       George
     Bill             5            2       David
     George           6            4       John
END DATA.
SORT    CASES BY Wrestler Tournament.
DATASET NAME     Wrestlers WINDOW=FRONT.