Using the Maximum function to update a field...?

[John Dodson]

Hello All,

I have a one to many set. Set1 has the key field "Symbol", Set2 has key field "Symbol" and field "Volume".

For a report I can create a Report Group called Symbol and calculate the maiximum volume number within that group eg
maximum(Set2->volume, GRP->Symbol)

Rather than calculate this on the Fly using the report writer I would like to update a field in Set1 with this number. I cannot figure out how to do this. Any help appreciated.

Thanks!
John

[Stan Mathews]

Re: Using the Maximum function to update a field...?

Tablemax() or dbmax().

Update expression for set1 field will look something like

if symbol is character type

tablemax("set2","symbol = "+quote(symbol),"volume")

if symbol is numeric type

tablemax("set2","symbol = "+symbol,"volume")

[John Dodson]

Re: Using the Maximum function to update a field...?

Many thanks Stan, that did the trick!

[John Dodson]

Re: Using the Maximum function to update a field...?

Hi Stan,

Maybe you could help me figure out a good approach to another problem. (I'll try to be more concise with terminology, the above example should have said table1 and table2 instead of set1 and set2)

This is the same database which analyzes stock data. I have a one to many set. Table1->Table2. Table1 contains the field Symbol, Table2 the Fields Symbol, Date, Volume. Every day Table2 is updated with todays data and contains about 120 days worth of data. I analyse Table2 and update Table1 with a Flag. (and currently append to another table (Table3) with these flagged Symbols.

What I would like to do is to follow the flagged records for 5 days _after_ they were flagged. So if Table3 is used, it will have a number of Symbols, with up to 5 different start dates that I want to report on using Table2 to grab to 5 follow on days.

Any thoughts on how to go about this?

Thanks,
John

[Stan Mathews]

Re: Using the Maximum function to update a field...?

I really don't follow your description of the issue.

How/what would you do if you had to do it manually?

[John Dodson]

Re: Using the Maximum function to update a field...?

OK. Every day, in my in basket i get an updated list of stock volumes to check. I read through all the updates and select a few from that days updates to follow for the next 5 days. I put those in inbox number 2. I also go through inbox number 2 and get rid of any I had been following if they are more than 5 days old. so one big rotating bin, that i sort through and pick out a smaller rotating bin. I want to produce a report listing those items in inbox #2. e.g.

MSFT 03-15-14 100,000 (this is the flagged date)
and then all records from 03-16-15 through 03-20-14 (depends on when top record was flagged, how many are listed

GOOG 03-17-14 200.000
and then all records from 03-18-15 through (depends on when top record was flagged, how many are listed, 1 added every day until you get to 5 and then they are all gone.

Does that help explain?

thanks!

[John Dodson]

Re: Using the Maximum function to update a field...?

Never mind Stan, I've got it figured out --- just took a couple of days of pondering, solution is always easier than you imagine.

Thanks!

[Stan Mathews]

Re: Using the Maximum function to update a field...?

Glad to hear it. My thinking patterns don't seem to fit what you wanted to do.