Formala to find the best/latest signal

[Eskimo]

Hello,

I have a series of rows 14000+ lines strong in an excel sheet.

The series contain caribou location reports from those caribou that have a
collar attached to them.

Along with other information, each row contains three important columns
related to this question. Collar Number, Date and LC (LC = Position Fix
quality or Signal Strength).

I need a formula that will find the best and latest signal strength for each
collar and for each day!. The formula should result in a 0 or 1. (no or yes).

There are about 30 different collars each reporting on certain days. The LC
values are 1 to 7 with 7 being the strongest.

If anyone can find me a solution, that would be just excellent.

Thanks in advance,

Jonathan

 

[Duke Carey]

How does 'best and latest' for each day translate into a No or Yes? That
seems a tad contradictory

 

[Eskimo]

Hi Duke,

I guess that it does not have to be a yes or no, that is how I used a
formula in the past to find the best/latest day.

How can I explain this. Each collar makes a position "fix" everyday. Each
Fix's quality is not the same, so that in any one day, the fix can range from
1 - 7 with 7 being the best signal. However, there can be more than one fix
with a 7 signal. There is also a time column, which I completely forgot to
include in the original information. Forgive me.

So the formula should decipher which of the fixes is the latest-best signal.
It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
the latest best row for that particuliar collar on that particuliar day.

Thanks,

 

[Duke Carey]

Ok, let's recap -

You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time
are not kept as a single value?). For any given collar you can have multiple
readings per day, and you want to find the time for the highest LC reading
(for each collar) each day, right?

This is much better handled in a database. Do you have MS Access? Do you
know how to link from Access to an Excel file? The Excel file is best laid
out with the column names in row 1, starting in col A and the data starting
in row 2. Nothing else should be on the sheet.

In Access use this query (assumes you name the linked sheet Collars, and the
columns are named Date/Time/Collar/LC)

SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC
FROM Collars
WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date)));

 

[Bernard Liengme]

Send me a sample file (remove TRUENORTH from my email address)
It is my understanding that "Eskimo" is no politically correct these days,
so I send best wishes to Inuit.

 

[Eskimo]

Hi Duke,

I have several columns of data, but your right, there are four columns that
we need for the purpose of this newsgroup question.

I can combine the date and time to use as a single value. They come separate
into the excel file so I just keep them that way. I also need the date value
as part of the end table/excel sheet report.

To further re-cap, I have about 30 different collars, each collar has
several rows of "fix" positions so there are also several rows of the same
collar. Within each group of rows of the same collars, there are several
dates. Within each collar/date group, there are several ascending times.
Now, each row has an "LC" (acronym for Location Class). LC being the quality
of the position fix. 1 being lowest - 7 being highest.

The thing to think about is that each collar's group which report on the
same day has varying LC quality throughout the day. The best LC for that
collar on that day could come before the last report went through on that
day!.

I'll try your Access suggestion, I am very familiar with Access and can link
the excel data. can I not just import the excel sheet into a new collar named
Collars?

Thanks,

Jonathan

 

[Duke Carey]

Can you just import into Access? Yes, but if the data is constantly added to
the Excel sheet then linking is better. The query of a linked sheet will
pick up any new data; otherwise you need to re-import each time you want to
run the query on new data.

The query I gave you will work just fine for the situation you have described

 

[Eskimo]

Hi Duke,

I tried running the query as you described, however when I clicked on run, a
message prompt appeared saying that "at most one record can be returned by
this subquery"

When I clicked on help, it says. a subquery of this kind cannot return more
that one record. revise the select statrment of the subquery to request only
one record".

Any thoughts?

Eskimo

 

[Eskimo]

Hi Bernard,

Your right, the word Eskimo is not politically correct. Me being one, it
don't bother me one bit. I am not very political and not easily insulted by
such a name.

I have sent you an example of the excel file by Email.

Jonathan

 

[Duke Carey]

Sorry Eskimo -

Try this one instead

SELECT c1.Date, c1.Time, c1.Collar, c1.LC
FROM Collars AS c1
inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date,
Collar) as c2
ON c1.Date = c2.Date
AND C1.Collar = c2.Collar
AND c1.LC = c2.MLC

 

[Eskimo]

Hi Duke,

The code worked but with an exception. It did give me the highest ranked
collar for each collar for each day, but it also gave me all of the same
ranked collars on the same day on a previous time.

I think we can run another query to remove the previous record(the same
collar, on the same day with the same rank, but on a previous time).

I will accept your suggestion now and thanks very much for your time.

Eskimo

 

[Duke Carey]

Try this modification:

SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC
FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from
Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND
(C1.Collar=c2.Collar) AND (c1.Date=c2.Date)
GROUP BY c1.Date, c1.Collar, c1.LC;

 

[Eskimo]

Works like a charm. Excellent work. Your the best.

I had this same question posted in Access portion of this newsgroup.

I'll post a thank you in that area direct to you also.

Thanks again.

Eskimo

Try this modification:

SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC
FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from
Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND
(C1.Collar=c2.Collar) AND (c1.Date=c2.Date)
GROUP BY c1.Date, c1.Collar, c1.LC;

Hi Duke,

The code worked but with an exception. It did give me the highest ranked
collar for each collar for each day, but it also gave me all of the same
ranked collars on the same day on a previous time.

I think we can run another query to remove the previous record(the same
collar, on the same day with the same rank, but on a previous time).

I will accept your suggestion now and thanks very much for your time.

Eskimo

 

[Eskimo]

Hi Duke, I was wrong, it did the same thing and included the same collar on
the same day with the same rank but with an earlier time.

hmm. tough one eh?

I'll try and work it out, if you have any further suggestions, that would be
great.

Thanks,

Jonathan

Try this modification:

SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC
FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from
Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND
(C1.Collar=c2.Collar) AND (c1.Date=c2.Date)
GROUP BY c1.Date, c1.Collar, c1.LC;

Hi Duke,

The code worked but with an exception. It did give me the highest ranked
collar for each collar for each day, but it also gave me all of the same
ranked collars on the same day on a previous time.

I think we can run another query to remove the previous record(the same
collar, on the same day with the same rank, but on a previous time).

I will accept your suggestion now and thanks very much for your time.

Eskimo

 

[Eskimo]

My wrong again.

It did work exactly as I wanted.

It caused an error when I tried to include the other columns of information
relevant to the table. It would just keep adding all values within the
original table.

Thanks again, I'll keep plugging at it.

Eskimo

Try this modification:

SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC
FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from
Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND
(C1.Collar=c2.Collar) AND (c1.Date=c2.Date)
GROUP BY c1.Date, c1.Collar, c1.LC;

Hi Duke,

The code worked but with an exception. It did give me the highest ranked
collar for each collar for each day, but it also gave me all of the same
ranked collars on the same day on a previous time.

I think we can run another query to remove the previous record(the same
collar, on the same day with the same rank, but on a previous time).

I will accept your suggestion now and thanks very much for your time.

Eskimo