Field calculation if-then statement for range

Yahoo Archive
1
Yeah I think you're right... The BAQ progress interpreter for calc fields just assumes the End.'s based upon the If, Else, If... statement layout.
Thanks Rob
Rob




________________________________
From: Rob Bucek <rbucek@...>
To: vantage@yahoogroups.com
Sent: Wednesday, May 20, 2009 2:30:22 PM
Subject: RE: [Vantage] Re: Field calculation if-then statement for range





You shouldn't need those End statements either...heres a calculated
field in a BAQ for a decimal column I wrote that works well...

if labordtl.labortype= "I" then if labordtl.indirectco de="PMB" then 0
else if labordtl.indirectco de="STB" then 0 else labordtl.laborhrs else 0

Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg com/>

(Click the logo to view our site)

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of bw2868bond
Sent: Wednesday, May 20, 2009 10:53 AM
To: vantage@yahoogroups .com
Subject: [Vantage] Re: Field calculation if-then statement for range

The calculated field you are creating in your BAQ has the data type of
string ?

--- In vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com> ,
"emverbeke" <emverbeke@. ..> wrote:
>
> That didn't work. I'm still getting an error. I tried it with and
without the parenthesis. It is a calculation on the
QuoteHed.Confidence Pct field. So this is what I have.
> If QuoteHed.Confidence Pct <= .25 then
> "25%"
> Else
> If QuoteHed.Confidence Pct <= .5 then
> "50%"
> Else
> If QuoteHed.Confidence Pct <= .75 then
> "75%"
> Else
> "100%"
> End.
> End.
> End.
>
> --- In vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com> ,
Robert Brown <robertb_versa@ > wrote:
> >
> > It's a calc field using simple progress 4GL statements:
> >
> > (If yourtable.yourfield <= .25 then
> > "25%"
> > Else
> > (If yourtable.yourfield <= .5 then
> > "50%"
> > Else
> > (If yourtable.yourfield <= .75 then
> > "75%"
> > Else
> > "100%"
> > End.)
> > End.)
> > End.)
> >
> > Technically, I'm not sure you even need the parthensis. The OpenEdge
Progress 4GL Handbook has examples of nexted IF statements without using
parenthesis. If you get an error when testing the BAQ, try it without.
> >
> > Also: For this simple example, I'm using yourtable.yourfield in
place of a true table.field name. If you are actually calculating the
yourtable.yourfield value in another calculated field in your BAQ, you
can NOT reference that calculated field by name in another calculated
field.
> >
> > You would need to imbed that initial calculation within the
statement above in place of yourtable.yourfield (at least on all
v8.03.305-405a versions I've used).
> >
> > The BAQ editor is an extremely 'dumbed down' application that limits
the true power of Progress. To get all of the power progress 4GL offers,
you would have to purchase the progress development environment (for no
small fee!).
> >
> > This is why Progress is Progress (I fne RDMS & 4GL language) and
Microsoft (SQL Server) and even Oracle are (relative to each other in
size) are who they are.
> >
> > Microsoft practically BEGS you to use their tremendous online
resources (TechNet) that is littered with free to use code examples &
their development environments, while not free, are certainly not
financial back-breakers either.
> >
> > That said, Progress IS and excellent engine and language... There
are many things it can do in a single statement that require much more
SQL equivalent result code.
> >
> > Rob
> >
> >
> >
> >
> >
> > ____________ _________ _________ __
> > From: emverbeke <emverbeke@>
> > To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
> > Sent: Monday, May 18, 2009 10:47:19 AM
> > Subject: [Vantage] Field calculation if-then statement for range
> >
> >
> >
> >
> >
> > I'm working with a query and want to be able to say that if the
confince percentage is between 1 and 25 to put 25 into a new field or if
the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC
document as well as the 4GL book but I'm not have much luck. Any ideas?
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
2
I'm working with a query and want to be able to say that if the confince percentage is between 1 and 25 to put 25 into a new field or if the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC document as well as the 4GL book but I'm not have much luck. Any ideas?
3
It's a calc field using simple progress 4GL statements:

(If yourtable.yourfield <= .25 then
 "25%"
  Else
 (If yourtable.yourfield <= .5 then
    "50%"
     Else
     (If yourtable.yourfield <= .75 then
        "75%"
        Else
        "100%"
        End.)
   End.)
End.)

Technically, I'm not sure you even need the parthensis. The OpenEdge Progress 4GL Handbook has examples of nexted IF statements without using parenthesis. If you get an error when testing the BAQ, try it without.

Also: For this simple example, I'm using yourtable.yourfield in place of a true table.field name. If you are actually calculating the yourtable.yourfield value in another calculated field in your BAQ, you can NOT reference that calculated field by name in another calculated field.

You would need to imbed that initial calculation within the statement above in place of yourtable.yourfield (at least on all v8.03.305-405a versions I've used).

The BAQ editor is an extremely 'dumbed down'Â application that limits the true power of Progress. To get all of the power progress 4GL offers, you would have to purchase the progress development environment (for no small fee!).

This is why Progress is Progress (I fne RDMS & 4GL language) and Microsoft (SQL Server) and even Oracle are (relative to each other in size) are who they are.

Microsoft practically BEGS you to use their tremendous online resources (TechNet) that is littered with free to use code examples & their development environments, while not free, are certainly not financial back-breakers either.

That said, Progress IS and excellent engine and language... There are many things it can do in a single statement that require much more SQL equivalent result code.

Rob





________________________________
From: emverbeke <emverbeke@...>
To: vantage@yahoogroups.com
Sent: Monday, May 18, 2009 10:47:19 AM
Subject: [Vantage] Field calculation if-then statement for range





I'm working with a query and want to be able to say that if the confince percentage is between 1 and 25 to put 25 into a new field or if the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC document as well as the 4GL book but I'm not have much luck. Any ideas?







[Non-text portions of this message have been removed]
4
That didn't work. I'm still getting an error. I tried it with and without the parenthesis. It is a calculation on the QuoteHed.ConfidencePct field. So this is what I have.
If QuoteHed.ConfidencePct <= .25 then
"25%"
Else
If QuoteHed.ConfidencePct <= .5 then
"50%"
Else
If QuoteHed.ConfidencePct <= .75 then
"75%"
Else
"100%"
End.
End.
End.

--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...> wrote:
>
> It's a calc field using simple progress 4GL statements:
>
> (If yourtable.yourfield <= .25 then
> Â "25%"
> Â Â Else
>  (If yourtable.yourfield <= .5 then
> Â Â Â Â "50%"
> Â Â Â Â Â Else
>      (If yourtable.yourfield <= .75 then
> Â Â Â Â Â Â Â Â "75%"
> Â Â Â Â Â Â Â Â Else
> Â Â Â Â Â Â Â Â "100%"
> Â Â Â Â Â Â Â Â End.)
> Â Â Â End.)
> End.)
>
> Technically, I'm not sure you even need the parthensis. The OpenEdge Progress 4GL Handbook has examples of nexted IF statements without using parenthesis. If you get an error when testing the BAQ, try it without.
>
> Also: For this simple example, I'm using yourtable.yourfield in place of a true table.field name. If you are actually calculating the yourtable.yourfield value in another calculated field in your BAQ, you can NOT reference that calculated field by name in another calculated field.
>
> You would need to imbed that initial calculation within the statement above in place of yourtable.yourfield (at least on all v8.03.305-405a versions I've used).
>
> The BAQ editor is an extremely 'dumbed down'Â application that limits the true power of Progress. To get all of the power progress 4GL offers, you would have to purchase the progress development environment (for no small fee!).
>
> This is why Progress is Progress (I fne RDMS & 4GL language) and Microsoft (SQL Server) and even Oracle are (relative to each other in size) are who they are.
>
> Microsoft practically BEGS you to use their tremendous online resources (TechNet) that is littered with free to use code examples & their development environments, while not free, are certainly not financial back-breakers either.
>
> That said, Progress IS and excellent engine and language... There are many things it can do in a single statement that require much more SQL equivalent result code.
>
> Rob
>
>
>
>
>
> ________________________________
> From: emverbeke <emverbeke@...>
> To: vantage@yahoogroups.com
> Sent: Monday, May 18, 2009 10:47:19 AM
> Subject: [Vantage] Field calculation if-then statement for range
>
>
>
>
>
> I'm working with a query and want to be able to say that if the confince percentage is between 1 and 25 to put 25 into a new field or if the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC document as well as the 4GL book but I'm not have much luck. Any ideas?
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
5
The calculated field you are creating in your BAQ has the data type of string ?

--- In vantage@yahoogroups.com, "emverbeke" <emverbeke@...> wrote:
>
> That didn't work. I'm still getting an error. I tried it with and without the parenthesis. It is a calculation on the QuoteHed.ConfidencePct field. So this is what I have.
> If QuoteHed.ConfidencePct <= .25 then
> "25%"
> Else
> If QuoteHed.ConfidencePct <= .5 then
> "50%"
> Else
> If QuoteHed.ConfidencePct <= .75 then
> "75%"
> Else
> "100%"
> End.
> End.
> End.
>
> --- In vantage@yahoogroups.com, Robert Brown <robertb_versa@> wrote:
> >
> > It's a calc field using simple progress 4GL statements:
> >
> > (If yourtable.yourfield <= .25 then
> > Â "25%"
> > Â Â Else
> >  (If yourtable.yourfield <= .5 then
> > Â Â Â Â "50%"
> > Â Â Â Â Â Else
> >      (If yourtable.yourfield <= .75 then
> > Â Â Â Â Â Â Â Â "75%"
> > Â Â Â Â Â Â Â Â Else
> > Â Â Â Â Â Â Â Â "100%"
> > Â Â Â Â Â Â Â Â End.)
> > Â Â Â End.)
> > End.)
> >
> > Technically, I'm not sure you even need the parthensis. The OpenEdge Progress 4GL Handbook has examples of nexted IF statements without using parenthesis. If you get an error when testing the BAQ, try it without.
> >
> > Also: For this simple example, I'm using yourtable.yourfield in place of a true table.field name. If you are actually calculating the yourtable.yourfield value in another calculated field in your BAQ, you can NOT reference that calculated field by name in another calculated field.
> >
> > You would need to imbed that initial calculation within the statement above in place of yourtable.yourfield (at least on all v8.03.305-405a versions I've used).
> >
> > The BAQ editor is an extremely 'dumbed down'Â application that limits the true power of Progress. To get all of the power progress 4GL offers, you would have to purchase the progress development environment (for no small fee!).
> >
> > This is why Progress is Progress (I fne RDMS & 4GL language) and Microsoft (SQL Server) and even Oracle are (relative to each other in size) are who they are.
> >
> > Microsoft practically BEGS you to use their tremendous online resources (TechNet) that is littered with free to use code examples & their development environments, while not free, are certainly not financial back-breakers either.
> >
> > That said, Progress IS and excellent engine and language... There are many things it can do in a single statement that require much more SQL equivalent result code.
> >
> > Rob
> >
> >
> >
> >
> >
> > ________________________________
> > From: emverbeke <emverbeke@>
> > To: vantage@yahoogroups.com
> > Sent: Monday, May 18, 2009 10:47:19 AM
> > Subject: [Vantage] Field calculation if-then statement for range
> >
> >
> >
> >
> >
> > I'm working with a query and want to be able to say that if the confince percentage is between 1 and 25 to put 25 into a new field or if the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC document as well as the 4GL book but I'm not have much luck. Any ideas?
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
6
You shouldn't need those End statements either...heres a calculated
field in a BAQ for a decimal column I wrote that works well...



if labordtl.labortype="I" then if labordtl.indirectcode="PMB" then 0
else if labordtl.indirectcode="STB" then 0 else labordtl.laborhrs else 0



Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site)



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of bw2868bond
Sent: Wednesday, May 20, 2009 10:53 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Field calculation if-then statement for range





The calculated field you are creating in your BAQ has the data type of
string ?

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"emverbeke" <emverbeke@...> wrote:
>
> That didn't work. I'm still getting an error. I tried it with and
without the parenthesis. It is a calculation on the
QuoteHed.ConfidencePct field. So this is what I have.
> If QuoteHed.ConfidencePct <= .25 then
> "25%"
> Else
> If QuoteHed.ConfidencePct <= .5 then
> "50%"
> Else
> If QuoteHed.ConfidencePct <= .75 then
> "75%"
> Else
> "100%"
> End.
> End.
> End.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
Robert Brown <robertb_versa@> wrote:
> >
> > It's a calc field using simple progress 4GL statements:
> >
> > (If yourtable.yourfield <= .25 then
> > "25%"
> > Else
> > (If yourtable.yourfield <= .5 then
> > "50%"
> > Else
> > (If yourtable.yourfield <= .75 then
> > "75%"
> > Else
> > "100%"
> > End.)
> > End.)
> > End.)
> >
> > Technically, I'm not sure you even need the parthensis. The OpenEdge
Progress 4GL Handbook has examples of nexted IF statements without using
parenthesis. If you get an error when testing the BAQ, try it without.
> >
> > Also: For this simple example, I'm using yourtable.yourfield in
place of a true table.field name. If you are actually calculating the
yourtable.yourfield value in another calculated field in your BAQ, you
can NOT reference that calculated field by name in another calculated
field.
> >
> > You would need to imbed that initial calculation within the
statement above in place of yourtable.yourfield (at least on all
v8.03.305-405a versions I've used).
> >
> > The BAQ editor is an extremely 'dumbed down' application that limits
the true power of Progress. To get all of the power progress 4GL offers,
you would have to purchase the progress development environment (for no
small fee!).
> >
> > This is why Progress is Progress (I fne RDMS & 4GL language) and
Microsoft (SQL Server) and even Oracle are (relative to each other in
size) are who they are.
> >
> > Microsoft practically BEGS you to use their tremendous online
resources (TechNet) that is littered with free to use code examples &
their development environments, while not free, are certainly not
financial back-breakers either.
> >
> > That said, Progress IS and excellent engine and language... There
are many things it can do in a single statement that require much more
SQL equivalent result code.
> >
> > Rob
> >
> >
> >
> >
> >
> > ________________________________
> > From: emverbeke <emverbeke@>
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Sent: Monday, May 18, 2009 10:47:19 AM
> > Subject: [Vantage] Field calculation if-then statement for range
> >
> >
> >
> >
> >
> > I'm working with a query and want to be able to say that if the
confince percentage is between 1 and 25 to put 25 into a new field or if
the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC
document as well as the 4GL book but I'm not have much luck. Any ideas?
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>





[Non-text portions of this message have been removed]
7
Â
Looks like you have ran into a bug.
I have tried it in Version 8.03 and in E9
Both of them have correct syntax but will not work when you test it.
I just had the one table and two fields. It would show the systax as ok but would error out when I hit the test button.

--- On Wed, 5/20/09, bw2868bond <bwalker@...> wrote:


From: bw2868bond <bwalker@...>
Subject: [Vantage] Re: Field calculation if-then statement for range
To: vantage@yahoogroups.com
Date: Wednesday, May 20, 2009, 10:52 AM








The calculated field you are creating in your BAQ has the data type of string ?

--- In vantage@yahoogroups .com, "emverbeke" <emverbeke@. ..> wrote:
>
> That didn't work. I'm still getting an error. I tried it with and without the parenthesis. It is a calculation on the QuoteHed.Confidence Pct field. So this is what I have.
> If QuoteHed.Confidence Pct <= .25 then
> "25%"
> Else
> If QuoteHed.Confidence Pct <= .5 then
> "50%"
> Else
> If QuoteHed.Confidence Pct <= .75 then
> "75%"
> Else
> "100%"
> End.
> End.
> End.
>
> --- In vantage@yahoogroups .com, Robert Brown <robertb_versa@ > wrote:
> >
> > It's a calc field using simple progress 4GL statements:
> >
> > (If yourtable.yourfield <= .25 then
> > Â "25%"
> > Â Â Else
> >  (If yourtable.yourfield <= .5 then
> > Â Â Â Â "50%"
> > Â Â Â Â Â Else
> >      (If yourtable.yourfield <= .75 then
> > Â Â Â Â Â Â Â Â "75%"
> > Â Â Â Â Â Â Â Â Else
> > Â Â Â Â Â Â Â Â "100%"
> > Â Â Â Â Â Â Â Â End.)
> > Â Â Â End.)
> > End.)
> >
> > Technically, I'm not sure you even need the parthensis. The OpenEdge Progress 4GL Handbook has examples of nexted IF statements without using parenthesis. If you get an error when testing the BAQ, try it without.
> >
> > Also: For this simple example, I'm using yourtable.yourfield in place of a true table.field name. If you are actually calculating the yourtable.yourfield value in another calculated field in your BAQ, you can NOT reference that calculated field by name in another calculated field.
> >
> > You would need to imbed that initial calculation within the statement above in place of yourtable.yourfield (at least on all v8.03.305-405a versions I've used).
> >
> > The BAQ editor is an extremely 'dumbed down'Â application that limits the true power of Progress. To get all of the power progress 4GL offers, you would have to purchase the progress development environment (for no small fee!).
> >
> > This is why Progress is Progress (I fne RDMS & 4GL language) and Microsoft (SQL Server) and even Oracle are (relative to each other in size) are who they are.
> >
> > Microsoft practically BEGS you to use their tremendous online resources (TechNet) that is littered with free to use code examples & their development environments, while not free, are certainly not financial back-breakers either.
> >
> > That said, Progress IS and excellent engine and language... There are many things it can do in a single statement that require much more SQL equivalent result code.
> >
> > Rob
> >
> >
> >
> >
> >
> > ____________ _________ _________ __
> > From: emverbeke <emverbeke@>
> > To: vantage@yahoogroups .com
> > Sent: Monday, May 18, 2009 10:47:19 AM
> > Subject: [Vantage] Field calculation if-then statement for range
> >
> >
> >
> >
> >
> > I'm working with a query and want to be able to say that if the confince percentage is between 1 and 25 to put 25 into a new field or if the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC document as well as the 4GL book but I'm not have much luck. Any ideas?
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>



















[Non-text portions of this message have been removed]
8
What does the error message say?

Also - We don't use quote module so I'm not familiar with the tables.

I just looked at the data dictionary & now I think I see what the problem is. QuoteHed.ConfidencePct is an INTEGER field - so ALL the values (unless zero) are going to be greater than 1.

Example, the application might display entered confidence as "75%" - but actual data value is integer 75 (not .75 displayed as a percent format like you might do in excel).

Try adjusting your calc field Progress statement to:

If QuoteHed.Confidence Pct <= 25 then
"25%"
Else
If QuoteHed.Confidence Pct <= 50 then
"50%"
Else
If QuoteHed.Confidence Pct <= 75 then
"75%"
Else
"100%"
End.
End.
End.

(You might have to try it with and without the parenthesis to see which one works.)

Rob




________________________________
From: emverbeke <emverbeke@...>
To: vantage@yahoogroups.com
Sent: Wednesday, May 20, 2009 11:02:28 AM
Subject: [Vantage] Re: Field calculation if-then statement for range





That didn't work. I'm still getting an error. I tried it with and without the parenthesis. It is a calculation on the QuoteHed.Confidence Pct field. So this is what I have.
If QuoteHed.Confidence Pct <= .25 then
"25%"
Else
If QuoteHed.Confidence Pct <= .5 then
"50%"
Else
If QuoteHed.Confidence Pct <= .75 then
"75%"
Else
"100%"
End.
End.
End.

--- In vantage@yahoogroups .com, Robert Brown <robertb_versa@ ...> wrote:
>
> It's a calc field using simple progress 4GL statements:
>
> (If yourtable.yourfield <= .25 then
> Â "25%"
> Â Â Else
>  (If yourtable.yourfield <= .5 then
> Â Â Â Â "50%"
> Â Â Â Â Â Else
>      (If yourtable.yourfield <= .75 then
> Â Â Â Â Â Â Â Â "75%"
> Â Â Â Â Â Â Â Â Else
> Â Â Â Â Â Â Â Â "100%"
> Â Â Â Â Â Â Â Â End.)
> Â Â Â End.)
> End.)
>
> Technically, I'm not sure you even need the parthensis. The OpenEdge Progress 4GL Handbook has examples of nexted IF statements without using parenthesis. If you get an error when testing the BAQ, try it without.
>
> Also: For this simple example, I'm using yourtable.yourfield in place of a true table.field name. If you are actually calculating the yourtable.yourfield value in another calculated field in your BAQ, you can NOT reference that calculated field by name in another calculated field.
>
> You would need to imbed that initial calculation within the statement above in place of yourtable.yourfield (at least on all v8.03.305-405a versions I've used).
>
> The BAQ editor is an extremely 'dumbed down'Â application that limits the true power of Progress. To get all of the power progress 4GL offers, you would have to purchase the progress development environment (for no small fee!).
>
> This is why Progress is Progress (I fne RDMS & 4GL language) and Microsoft (SQL Server) and even Oracle are (relative to each other in size) are who they are.
>
> Microsoft practically BEGS you to use their tremendous online resources (TechNet) that is littered with free to use code examples & their development environments, while not free, are certainly not financial back-breakers either.
>
> That said, Progress IS and excellent engine and language... There are many things it can do in a single statement that require much more SQL equivalent result code.
>
> Rob
>
>
>
>
>
> ____________ _________ _________ __
> From: emverbeke <emverbeke@. ..>
> To: vantage@yahoogroups .com
> Sent: Monday, May 18, 2009 10:47:19 AM
> Subject: [Vantage] Field calculation if-then statement for range
>
>
>
>
>
> I'm working with a query and want to be able to say that if the confince percentage is between 1 and 25 to put 25 into a new field or if the percentage is 26-50 to put 50 and so on. I've looked t the 3643ESC document as well as the 4GL book but I'm not have much luck. Any ideas?
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>







[Non-text portions of this message have been removed]