Alpha Video Training
Results 1 to 10 of 10

Thread: Calculation Help

  1. #1
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,877

    Default Calculation Help

    I'm hoping that this is simple, but I just can't seem to get past it.

    We have an employee system where there is an employee table and attached to it is a position history "Child" table. We are trying to develop a report where we show the current title (that one is easy) and the first time the employee was "permanent". That is designated by a date in a field called "From" and a code of "RA" in a "status" field of the child record. Some employees have been here many years, so there are many Child records.

    What I am trying (unsuccessfully) to do is to get the first non-blank calc field "PERMANENT". I'm going around in circles.

    Here are the calcs:

    Code:
    firstperm	if(calc->Permanent="".and.calc->Firstperm="","",calc->Permanent)
    lasttitle	last(Titles->Title,GRP->Lfname)	
    PERMANENT	IF(POS_HIST->STATUS="RA", DTOC(POS_HIST->FROM), " ")	
    PROVISIONL	IF(POS_HIST->STATUS="PA", DTOC(POS_HIST->FROM), " ")	
    realfirsrt	if(calc->Firstperm<>"",calc->Firstperm,calc->Permanent)
    any thoughts would be appreciated.

    Tom:confused:

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Calculation Help

    Maybe

    TABLEMIN("POS_HIST","STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")

    Some employees have been here many years
    Might have to use an earlier year than 1900.

    Well, the first expression would be ok for the earliest employee. For an individual employee id.....

    TABLEMIN("POS_HIST","ID = "+quote(id)+" .and. STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")

    supplying the quoted id from the current record in the report.
    There can be only one.

  3. #3
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Calculation Help

    What I am trying (unsuccessfully)
    how was it unsuccessful? Did it error due to your setting the second (false) part of the if() to a character instead of a date-- you used " " instead of {} - curly brackets which are for a empty date value.


    EDIT--which Stan's expression obviously eliminates!
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Calculation Help

    Is the "RA" status field value appearing in multiple records for an employee, and you're trying to find the first one? Some sample data would help us offer suggestions.

    In the absence of sample data I'm thinking you could build a custom UDF that will open the "child" table, indexed on the employee id number. Fetch_find the first related child table record, then step through them until you find a record that has the right status value. Return the date field (or its character equivalent) from the function.

    If performance becomes an issue you could offload the desired status field values to a separate table, once, using a batch processing script, and then record first RA transactions there going forward.

    Last notion would be to reverse the table relationships in the set, and filter the primary table to only show records that have the right status field values.

  5. #5
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Calculation Help

    I see Stan is suggesting something similar, but with much less coding required. Would be interesting to see if the tablemin() approach was faster than the indexed find by key sequence I described. Both will require alpha to open a new instance of the child table for each employee.

  6. #6
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,877

    Default Re: Calculation Help

    Thanks for all your replies.

    Stan,
    TABLEMIN() shows the first instance of the date in the table. I just need it for the subset of records pertaining to the particular employee. I even put an additional filter in your code to make it test the Employee->empID against the POS_HIST->EMPID, to no avail.

    Code:
    TABLEMIN("POS_HIST","Pos_Hist->Emp_Id=Emp_Id.and.STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")
    Mike,
    The error I was getting was that in the "firstperm" calc, I was testing the firstperm calc field, and I don't think Alpha can handle that.

    Tom,
    you gave me an idea to build another set with just "RA" type records. I'll test that out and let you know.

    To all, thanks for your suggestions.

    Tom

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Calculation Help

    It worked here with a test variable. I would explicitly pass the emp_id value into the function.


    Code:
    TABLEMIN("POS_HIST","Pos_Hist->Emp_Id="+quote(Employee->empID)+" .and. STATUS = 'RA' .AND. FROM > {01/01/1900}","FROM")
    There can be only one.

  8. #8
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,877

    Default Re: Calculation Help

    Stan,

    I'll give it a whirl, thanks.

    Tom

  9. #9
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,877

    Default Re: Calculation Help

    Got it working. Thanks STAN!

    It turned out the the field EMP_ID is numeric. That causes all sorts of issues in a character string filter.

    Here is the code that works:

    Code:
    TABLEMIN("POS_HIST","Pos_Hist->Emp_Id="+val(str(Emp_Id))+".and.STATUS = 'RA' .AND. FROM > {01/01/1960}","FROM")
    The report carries on for a little bit, but since I get the results I need, I don't care.

    Again, Thanks.

    Tom:)
    Last edited by Tom Henkel; 03-01-2011 at 12:44 PM. Reason: can't type

  10. #10
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Calculation Help

    We should be able to speed it up using dbmin() rather than tablemin() but I'll have to work on that. Michael Humby showed me how one time but I've never utilized it.

    It was dbsum() that I was remembering. Don't see how to make it work with dbmin().

    The filter for the tablemin() could use isdate() to invalidate the blank "from" values rather than that fixed method I suggested.

    +" .and. status='RA' .and. isdate(dtoc(from))"
    Last edited by Stan Mathews; 03-01-2011 at 02:22 PM.
    There can be only one.

Similar Threads

  1. Calculation Help
    By reynolditpi in forum Alpha Five Version 8
    Replies: 8
    Last Post: 01-05-2008, 09:52 PM
  2. Calculation help
    By jonnielaw in forum Alpha Five Version 7
    Replies: 10
    Last Post: 06-06-2006, 01:20 AM
  3. calculation
    By Imran Ahmad in forum Alpha Five Version 5
    Replies: 1
    Last Post: 06-29-2003, 11:41 PM
  4. Calculation, calculation, calculation, etc., etc.
    By davej in forum Alpha Five Version 5
    Replies: 8
    Last Post: 05-20-2003, 07:51 AM
  5. Need help with calculation
    By Wanda Friesen in forum Alpha Five Version 4
    Replies: 2
    Last Post: 08-16-2000, 09:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •