1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Very off topic: Ternary Expressions in computing etc - need some help, please

Discussion in 'Off Topic' started by Kenny_Wisdom, Jan 19, 2010.

  1. Kenny_Wisdom

    Kenny_Wisdom Computer stained fingers

    I was hoping someone with some computing knowledge might be able to help me?

    I’m using a report generator in a project accounting package called “Causeway Project Accounting”, which might seem old fashioned, as there is supposed to be a method of exporting the report directly into Excel in order to use the increased functionality that is deemed to offer. However, although we technically are paying the license fee for access to that service, we don’t actually have access due to some other political reason or other which I shan’t even bother going into! You might also suggest just cutting and pasting the report into excel then using the “text – to – columns” feature to then apply the formula required – technically possible, but that then entails a lot of tidying up, deleting superfluous title rows which have exported from the original report etc. Right, that’s the background over with:

    The Problem:
    Let’s say I have produced a report which looks like this:

    Code Value
    H00128 3710.74
    H00801 999.10
    H00988 3923.79

    Within the report generator I can set calculations, which should return values accordingly. For example, I want to apply the rule that for every value below 1000, 10% is added. The calculation (extension) would look like this:
    (C2<1000) *C2*1.10
    i.e. If a value column 2 is below 1000, multiply the existing value by 1.10, which will add 10% to the value. I then have to tell the report where to print the new result, so let’s put that in column 3 and call it ‘+%’.

    Code Value +%
    H00128 3710.74 0.00
    H00808 999.10 1099.01
    H00984 3923.79 0.00

    That’s okay, but I want to apply the rule that say, for values over 1000 I want to add 50%. I can easily add a new column, and apply the formula similarly to the last 1 e.g. (C2>1000) *C2*1.50.
    What I really want to do is to print all the results in one column, which I think is when I need to use a ternary expression, but everything I try fails!

    I’ve got the manual out, which has one tiny section on Report Calculations in it:

    “When defining ‘extensions’ or ‘exclusions’ within the column definitions for reports, a calculation syntax is available which allows column numbers to be acted on by mathematical and logical operators.
    For example, an extension may be: C1*100/C2 (show column 1 as a percentage of column 2).
    The available operators are:
    + (add)
    * (multiply)
    % (modulo)
    < (less than)
    <=(less than or equal to)
    !=(not equal to)
    ||(logical OR)
    ?: Ternary expression (see below)
    - (subtract)
    / (divide)
    > (greater than)
    >= (greater than or equal to)
    ==(equal to)
    && (logical AND)
    !(negate)

    The logical operators would be used for an exclusion test: for example, C1==0 ||(C5==0 &&C6==0) would not print the line if column 1 was zero, or both columns 5 and 6 were zero. Note the use of brackets to combine expressions in order to force the correct precedence.
    The ternary expression listed above can be used to return a value depending on the result of a test. For example, within an extension column: (C1!=0)?C2*C3:C4*C3 which states: print the product of columns 2 and 3 if column 1 is not zero, otherwise print the product of columns 4 and 3”

    From that, I thought I needed to write something like this:

    (C2!<1000)?C2*1.10:C2*1.50

    Thinking that it provides the instruction to ascertain if C2 is under 1000, in which case multiply by 1.10, and otherwise multiply by 1.50. That doesn’t work in my report, returning a zero value all the way.
    Ideally, I’d prefer to create multiple rules in the expression, such as:
    If below 1000, add 10%. If above 1000 but below 2000 add 20%. If above 2000, but below 3000 add 30% and so on.

    Any ideas, please?
     
  2. Kenny_Wisdom

    Kenny_Wisdom Computer stained fingers

    I think I have a ‘workaround’ now. The following seems to work, but it has to go across seperate columns. I can't seem to get the rules to apply to just one column

    Assuming value is in column 1 – in reality it could be any column: (Where X is a numeral)

    (C1>X)&&(C1<X) then, assuming this creates column 2:

    (C2>0) *C1*X

    which produces a result in the new column 2.

    And so forth, just replacing the column number and the x values accordingly. Seems to work! Any budding experts on this type of thing can see a better way, please jump in! In the end it doesn't seem like a ternary expression was the answer and I would have prefeered just to create the result in one column, but to be honest I've lost the will to live on something that should be so simple!
     
  3. SHAUN I

    SHAUN I It's so clear on the wings of the dawn

    Me too Kenny :biggrin:
     
    Last edited by a moderator: Jan 19, 2010
  4. aspwatterson

    aspwatterson The Unknown Soldier

    And me!


    At least I learnt a new word out of it all!

    ternary andi
     
  5. Steve_Pitts

    Steve_Pitts I've got a zappy little nappy

    A little after the fact, and from your later post you may not care any more, but...
    From the look of this expression and the others that you've listed, this is all very similar to C language syntax and that is the format of a ternary expression in C (ie. a logical expression that is treated as either true or false, followed by a question mark, followed by an expression that is evaluated as the value for the true condition, followed by a colon and then an expression for the false condition). However, the !< is not a valid condition and it may be that caused your expression not to have the desired effect. Try:

    (C2<1000)?C2*1.5:C2*1.1

    Assuming that the syntax follows C the ! is used to negate a condtion as a monadic operator and is also valid in != for not equals, so !(a=b) is the same as (a!=b) but the reverse of a<b is either a>=b or !(a<b)
     
    Last edited: Jan 23, 2010
  6. aspwatterson

    aspwatterson The Unknown Soldier

    Whats?

    What's the collective noun for computer programmers? I can think of some as a piece of fun actually but wouldn't dare mention it on here!

    andi:biggrin:
     
  7. Kenny_Wisdom

    Kenny_Wisdom Computer stained fingers

    Steve, thanks for that!! I'll try it at work tomorrow and let you know the outcome. It looks logical to me!
     
  8. Kenny_Wisdom

    Kenny_Wisdom Computer stained fingers

    Steve,

    Brilliant! It works.

    I presume from the "logic" of the expression, (i.e. The expression asks whether the condition is met, or not. If it is met, apply rule 1. If it is not met, apply rule 2) it's not possible to create a multiple condition in the same "column"? e.g. If <= 1000, add 10%, if <=2000 add 20%, and <=3000 add 30% etc?

    Never mind that too much - I like seeing how these things work and I do admit to getting a warm glow on those rare occasions when I can actually follow it all! :biggrin:
     
  9. Steve_Pitts

    Steve_Pitts I've got a zappy little nappy

    I wouldn't worry about it Andi, I've heard it all before (as has Paul, I'm sure). I've been programming for over thirty years and was a geek (in the modern sense) before the word meant what it means now. Whilst being a programmer has never been fashionable it has fluctuated in its degrees of unfashionability and we'll take the rough with the smooth.

    Given that the expressions (or parts thereof) could themselves be ternary expressions then theoretically you could nest them to your hearts content to achieve pretty much anything you need. However, there may be limits to their implementation within the tool you are using and they quickly become very difficult to read and understand, even if you were the person that wrote them just a few months ago. Personally (in C) I tend to use them only when they produce a neat one liner rather than a multi-line IF statement, but your example above could be written as:

    (c2<1000) ? c2*1.1 : ((c2 < 2000) ? c2*1.2 : ((c2 < 3000) ? c2*1.3 : c2*1.5))

    with the nesting brackets being unnecessary and added more to aid the human eye than anything else.
     
  10. Kenny_Wisdom

    Kenny_Wisdom Computer stained fingers

    Steve,

    Many thanks again for your time and effort on this.

    'Ye olde report generator' doesn't seem to like the second multi-nesting example, and then it was compounded by the fact I have just discovered there is a character limit within the report generator for that "line" of instruction.

    I think with all the guidance you have given me I can A) Create the report I need but most importantly B) I've enjoyed increasing my understanding of how the expressions work and follow what is quite a logical path which was a good refresher course for me!

    Stick an invoice in the post and I'll see it doesn't get paid, but the thought was there, matey! :cheers2: (I'll buy you a pint next time Roy gigs :wink:)
     
  11. Steve_Pitts

    Steve_Pitts I've got a zappy little nappy

    That's the nice thing about computers, everything can be kept at a level where it is all perfectly logical (not always possible, depending on the tools available, obviously).

    Anyway, happy to help and all advice is free (as in beer and speech)
     
  12. aspwatterson

    aspwatterson The Unknown Soldier

    Collective nouns suggests for computer programmers

    Gobbledegeek? Madtrix? GoogleComplex? Boringness?

    I think with my limited knowledge, and watching all my old Grammar school computer trained friends earn more then me, I'd go for :

    A jealousy of computer programmers.


    andi
     
  13. Steve_Pitts

    Steve_Pitts I've got a zappy little nappy

    All good Andi, but I prefer either an array or an assembly, although both require a certain amount of insider knowledge
     
  14. Kenny_Wisdom

    Kenny_Wisdom Computer stained fingers

    "An assembly of computer programmers" gets my vote.

    Great religious connotations too. I'll let Roy chew that one over.

    Mind you, with "Jerusalem" as the school song (us and a thousand others), I found some school assemblies quite stirring!

    :beatdeadhorse5:
     

Share This Page