Hello Everyone,
I‘ve a problem picking correct phone number. For Ex. The following example the person’s current resident phone number is 2’nd row and the phone number is “222-222-2222”. The I’st row is that person’s former address. For ex.If another person has ten former address, it is keep on adding at the top and current address is somewhere in the middle or last. Our phone number table data is little bit messed up. My problem is to hard pick the current resident phone number. I appreciate any thought regarding this issue.

Id pos Phone Number Phone Type
1111 1 111-111-1111 H
2222 2 222-222-2222 H
3333 3 333-333-3333 W

So how do you identify the current? Is there a date on the record or something like that? There has to be something there that you can test or sort on.

Thanks ginny for your quik reply,
I usally use where criteria WHERE POS EQ "1" for getting the current resident phone numer .But after the phone number migration phone table structure got changed. Now POS EQ "1" is former address phonenumber.

Vijaya

Nothing is going to work unless you can identify which one is current, either a date updated field newer than the rest or some sort of switch.

So how would you pick the correct number if it was all in Excel? (or printed out)

You need to give us more data to understand your problem.
You can sort by highest 1 xxx

As Ginny says it would be most unusual for a data source not to identify the current or active value.

However if it always the last entry, then using SUM on an alpha field always populates with the 'last'
value (providing you do not do anything to change the sort order).

for example

TABLE FILE CAR
PRINT MODEL
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
END
-RUN
TABLE FILE CAR
SUM MODEL
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
END

If you genuinely want some summated items and some print items use a multi-verb request.
(but again do not mess with the sort order of the incomming data.

TABLE FILE CAR
SUM MODEL
BY COUNTRY
PRINT MODEL
BY COUNTRY
END

Sort of like those Netflix radio ads.

Jack, That must be a US'ism because I have no idea what you're talking about.

JG: Yes it is. -- To rephrase that in more universal terms, the challenge seems analogous to solving 2 simultaneous linear equations in 5 variables: No solution without more information.

Back to topic:


VJ:

If there are no surviving attributes for dating or sequencing the rows ... My next tack would be a comparison (think MATCH FILE) of the present data-store against the original (you do have backups, right?). With a little experimentation one should be able to determine the most-current as of the old file's provenance date, and identify rows added or changed subsequently. Even if that leaves some residual ambiguities, it should reduce the volume to the point where it can be ironed out manually.

Can be done in two passes.
1st pass : create a HOLD1 file all the records WHERE REROCRDS OMITES '-' (as the line with - will be the phone record) along with the KEY

2nd Pass : create HOLD1 file for WHERE RECORD CONTAINS '-' along with KEY

Then you play the way you want.