Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Works in Excel..... but how in Alpha?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Works in Excel..... but how in Alpha?

    This formula gives me the right result in Excel:

    =WORTEL((D6-F6)*(D6-F6)+(E6-G6)*(E6-G6))*B6
    (Where "WORTEL" is Dutch for: SQRT)

    But how to get this to work in Alpha?
    The problem is not with the SQRT() command, it is with time issues.

    In Excel, the result cel is formatted like: uu:mm:ss d;@ so the result of the formula gives a time value.

    Cel B6 is also a time value.

    Now, in Excel these are the values:

    D6=500 (standard format)
    F6=426 (standard format)
    E6=503 (standard format)
    G6=426 (standard format)
    B6=0:18:00 (u:mm:ss)

    The result in Excel is: 08:02:18

    How to put this in Alpha's xbasic to give the same result?

    #2
    Re: Works in Excel..... but how in Alpha?

    Hoi

    It is amazing what SEEMS possible in excel.
    Formating cells hides real figures and confuses.
    If there was an option to display a date in Celsius it certainly would do it.
    So I doubt about the answer. I would suggest: Change the excelformat to standard and look step by step at your calculation.

    In this case if you want to calculate with time dimensions in Alpha Five:

    Convert the time elements to (time units of) seconds.
    Perform your calculation.
    Bring back the number of seconds in time.

    There are a lot of Date and Time functions described in the AlphaFive Help documentation.

    Have fun with it.

    Doei
    Most things are simple but unfortunately only after the first time

    Comment


      #3
      Re: Works in Excel..... but how in Alpha?

      What are you trying to do? Not the equation, but from what info to what info. My guess is that you are trying to get the absolute time between two time values,

      Alpha has a whole host of functions to deal with time as character, short time (time only) and long time (date & time) and numbers.

      Look at TOSECONDS(), TOTIME() and date-time functions, date-time conversion matrix
      Regards,

      Ira J. Perlow
      Computer Systems Design


      CSDA A5 Products
      New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
      CSDA Barcode Functions

      CSDA Code Utility
      CSDA Screen Capture


      Comment


        #4
        Re: Works in Excel..... but how in Alpha?

        Hi Ton, Ira,

        Let me explain a little bit more:

        D6,F6,E6,G6 are coordinates of a location in integer numbers:
        D6=X1
        E6=Y1
        F6=X2
        G6=Y2
        So the location of 1 would here be: X=500,Y=503 in the matrix,
        and the location of 2 would here be: X=426 and Y=426 in the matrix.

        B6 is the speed of the unit that travels from location to location.

        The formula contains the calculation of the distance between the locations using Pythagoras, and, taking into account the travel speed of the unit (B6), the formula results with the traveling time.

        In Excel, it computes correctly. The resulting traveling time in the example computes to 08:02:18
        Unfortunately, as Ton says, Excel often seem to make it possible, but actually putting it in Alpha is not so simple.

        I came up with this in the field rules from the Alpha Travelling Time field:

        Code:
        CONVERT_TYPE(TOTIME(SQRT( (HERKOMST1-DOELWIT1)*(HERKOMST1-DOELWIT1)+(HERKOMST2-DOELWIT2)*(HERKOMST2-DOELWIT2) ) * REISTIJDEENHEID,2,0),"T")
        The expression builder swallows it, but obviously, that does not mean it is correct, and as the result is not computing correctly, it also obviously is not........ :o

        Can any of you maybe help me getting the Alpha xbasic right?

        Comment


          #5
          Re: Works in Excel..... but how in Alpha?

          I ran this code, but I'm not sure what the real units of variable REISTIJDEENHEID is in. It should be basically be time (in "hh:mm:ss" character format) per coordinate units traveled in that time (which is 1/speed)

          Code:
          HERKOMST1=500
          DOELWIT1=426
          
          HERKOMST2=503
          DOELWIT2=426
          
          REISTIJDEENHEID="0:18:00"
          distance=SQRT(((HERKOMST1-DOELWIT1)^2)+((HERKOMST2-DOELWIT2)^2))
          timeinsec=distance*toseconds(REISTIJDEENHEID)
          totaltime=totime(timeinsec,2,0)
          ui_msg_box("time",ltrim(str(distance))+", "+ltrim(str(timeinsec))+", "+totaltime)
          
          end
          A combined equation looks like this
          totaltime=totime(SQRT(((HERKOMST1-DOELWIT1)^2)+((HERKOMST2-DOELWIT2)^2)) * toseconds(REISTIJDEENHEID),2,0)
          Last edited by csda1; 11-30-2009, 12:47 PM. Reason: Correction to code
          Regards,

          Ira J. Perlow
          Computer Systems Design


          CSDA A5 Products
          New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
          CSDA Barcode Functions

          CSDA Code Utility
          CSDA Screen Capture


          Comment


            #6
            Re: Works in Excel..... but how in Alpha?

            Few comments:
            1-You left out what is the value of B6. Per your result, B6 should calculate to 0.111645. Not sure what speed is that?
            2-Excel does not automatically give you the result in ss:mm:hh. You format it that way.
            3-Seems to me you are trying to calculate the total time to travel from point A to point B. Without knowing how you calculate B6 can't give you a precise expression.
            Is it a fraction of hour per mile? or more likely, a fraction of hour per kilometer? Couldn't be, unless you are traveling either by a freight train or maybe a mule!
            Probably a freight train.

            Comment


              #7
              Re: Works in Excel..... but how in Alpha?

              Hi Ira.

              I very much appreciate your efforts to help me out!

              REISTIJDEENHEID contains the travel speed for a unit.
              It's table format is now N,2,0 and it holds an integer number stating the number of minutes that it takes for this unit to travel one field.

              Does this help?

              I am working at your code right now, but it does not seem to work yet, obviously because it still needs the traveltime /
              "reistijdeenheid" in it.

              Regards,

              Comment


                #8
                Re: Works in Excel..... but how in Alpha?

                Originally posted by G Gabriel View Post
                Few comments:
                1-You left out what is the value of B6. Per your result, B6 should calculate to 0.111645. Not sure what speed is that?
                2-Excel does not automatically give you the result in ss:mm:hh. You format it that way.
                3-Seems to me you are trying to calculate the total time to travel from point A to point B. Without knowing how you calculate B6 can't give you a precise expression.
                Is it a fraction of hour per mile? or more likely, a fraction of hour per kilometer? Couldn't be, unless you are traveling either by a freight train or maybe a mule!
                Probably a freight train.
                Hi Mr. Gabriel,

                Thank you for responding.
                Let me try to explain better what you are missing:

                1) B6 in the example contains the value "18" (N,2,0).
                2) I know. That is correct.
                3) B6 is not calculated, it is a given value. Each unit travels with only one, constant, speed. In case of the example, the speed can only be 18 minutes per field.

                This concerns a virtual matrix of many fields, on which units travel from A to B with a certain, given, speed.
                The formula I need is indeed meant to calculate the time it takes to get from A to B.
                Excel does calculate it perfectly in the formula as given in the initial post. However, and that is probably what you meant, Excel takes the conversion to time for it's account behind the curtain, and I do not now how it does that. That is probably what Ton also means. However, Alpha does not like that approach :) and wants the exact formula, which leaves me somewhat stunned.

                Hope one of you can help me out, otherwise, my hair will suffer dramatically from it..... :D

                Comment


                  #9
                  Re: Works in Excel..... but how in Alpha?

                  Per the values you provided, the distance between A & B is 106.7942
                  and I have no idea what is this distance measured in so we will simply say units.
                  The speed is 18 minutes per unit.
                  The expression to tell the total time is therefore very simple:
                  total time=speed * Distance.
                  I am sure you already know that. Your dilemma is how to express that in alpha as time?
                  There has been a gizillion questions asking how to express time difference as time etc. And I for one remember very well posting a function to do that, which is your best bet, a UDF.
                  In this example, the total time will be:
                  18*106.7942=1922.295503 minutes.
                  How do you translate that to days, hours, minutes and seconds?
                  Can you do me a favor and run a quick search on the board? I am pretty sure you will find that function and many more. If not, will re-write it.

                  Comment


                    #10
                    Re: Works in Excel..... but how in Alpha?

                    Well, the search (which I did of course before posting this) might not be needed anymore, because Ira's code (how could it be anyway else?) works. My problem was with the time presentation of Excel.
                    Excel says: 08:02:18 1 which I thougt was: eight hours two minutes and 18 seconds, BUT the 1 behind it also stands for an additional 24 hours (1 full day) which I did not recognize, so the calculation of Ira is completely correct!

                    Thanks guys for stepping in and trying to help me with this!

                    Regards,

                    Comment


                      #11
                      Re: Works in Excel..... but how in Alpha?

                      Originally posted by MRichard View Post
                      Hi Ira.

                      I very much appreciate your efforts to help me out!

                      REISTIJDEENHEID contains the travel speed for a unit.
                      It's table format is now N,2,0 and it holds an integer number stating the number of minutes that it takes for this unit to travel one field.

                      Does this help?
                      Yes, however if filling REISTIJDEENHEID with the field's value, I have converted REISTIJDEENHEID to seconds. To get the field's value of 18 to seconds, use the following line instead

                      timeperunit=fieldname * 60

                      In the original code, I made a copy error, and have corrected it in the original post. Here is the code using many intermediate values so you can see the progress

                      Code:
                      HERKOMST1=500
                      DOELWIT1=426
                      
                      HERKOMST2=503
                      DOELWIT2=426
                      
                      ' This is time in minutes
                      REISTIJDEENHEID="0:18:00"
                      
                      ' This is distance in units, approximately 106.794 units for above values
                      distance=SQRT(((HERKOMST1-DOELWIT1)^2)+((HERKOMST2-DOELWIT2)^2))
                      
                      ' Time/unit distance in seconds is 1080 seconds for above values
                      timeperunit=toseconds(REISTIJDEENHEID)
                      
                      ' Total time in seconds is 115338 seconds for above values
                      
                      ' Total time is 32:02:18  (32 hours, 2 minutes, 18 seconds) for above values
                      timeinsec=distance * timeperunit
                      
                      ' Convert seconds to hh:mm:ss
                      totaltime=totime(timeinsec,2,0)
                      
                      ui_msg_box("time",ltrim(str(distance,10,3))+", "+ltrim(str(timeperunit,10,3))+", "+ltrim(str(timeinsec))+", "+totaltime)
                      
                      end
                      Regards,

                      Ira J. Perlow
                      Computer Systems Design


                      CSDA A5 Products
                      New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
                      CSDA Barcode Functions

                      CSDA Code Utility
                      CSDA Screen Capture


                      Comment


                        #12
                        Re: Works in Excel..... but how in Alpha?

                        I got it working!
                        Thanks Ira for the work you put in to this, much obliged.....
                        It is appreciated.

                        Thnx,

                        Comment


                          #13
                          Re: Works in Excel..... but how in Alpha?

                          Originally posted by MRichard View Post
                          I got it working!
                          Thanks Ira for the work you put in to this, much obliged.....
                          It is appreciated.

                          Thnx,
                          Graag Gedaan! :)
                          Regards,

                          Ira J. Perlow
                          Computer Systems Design


                          CSDA A5 Products
                          New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
                          CSDA Barcode Functions

                          CSDA Code Utility
                          CSDA Screen Capture


                          Comment


                            #14
                            Re: Works in Excel..... but how in Alpha?

                            Originally posted by csda1 View Post
                            Graag Gedaan! :)
                            Amazing!
                            Most things are simple but unfortunately only after the first time

                            Comment


                              #15
                              Re: Works in Excel..... but how in Alpha?

                              Well, we already knew him as an amazing programmer, but a linguist? ;)

                              Comment

                              Working...
                              X