turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- If, then statements

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2011 12:52 PM

Can anyone tell me how to do if, then, else statements using EG? I have tried several methods using the computed columns function and advanced expression builder, have tried the help option, have searched the forum, have several books, but none give instructions that seem to work with what I want to do. Other posts in this forum say to use the CASE function, but there is no such thing in the functions I can select and it hasn't worked when I've tried to use it. Essentially what I need to do is set up a variable that is:

If x=a and y=a, then z=1, else z=2.

Any suggestions?

Thanks.

If x=a and y=a, then z=1, else z=2.

Any suggestions?

Thanks.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-18-2011 03:20 PM

Hello MonTanya,

You can do it but better to use direct coding it is equal in efforts. Anyway,

1. Open quiery builder for your dataset

2. Click on Add a New Computed Column

3. Sellect Advanced Expression and Click Next

4. In the field Build Expression write the following:

case when x=a and y=a then 1

else 2

end

5. Click Next

6. Enter z into Identifier field and click Next

7. Click Finish

Sincerely,

SPR

You can do it but better to use direct coding it is equal in efforts. Anyway,

1. Open quiery builder for your dataset

2. Click on Add a New Computed Column

3. Sellect Advanced Expression and Click Next

4. In the field Build Expression write the following:

case when x=a and y=a then 1

else 2

end

5. Click Next

6. Enter z into Identifier field and click Next

7. Click Finish

Sincerely,

SPR

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-21-2011 04:58 PM

There is a pretty straightforward logical expression to get that result for z in the advanced expression builder:

1*(x=a & y=a) + 2*(^(x=a & y=a))

"(x=a & y=a)" is a logical expression and always resolves to a 0 or 1, so the multiplication works cleanly.

If you need for Z to be missing if X, Y, or A are missing (and they are numeric variables), you can expand the expression:

1*(x=a & y=a) + 2*(^(x=a & y=a)) + 0*x*y

(For the case equivalent to an if-then-else in the EGuide Query Builder, you need to look at the documentation for the CASE expression in the SQL reference manual, not the CASE statement, as they are a bit different.)

Doc Muhlbaier

Duke

1*(x=a & y=a) + 2*(^(x=a & y=a))

"(x=a & y=a)" is a logical expression and always resolves to a 0 or 1, so the multiplication works cleanly.

If you need for Z to be missing if X, Y, or A are missing (and they are numeric variables), you can expand the expression:

1*(x=a & y=a) + 2*(^(x=a & y=a)) + 0*x*y

(For the case equivalent to an if-then-else in the EGuide Query Builder, you need to look at the documentation for the CASE expression in the SQL reference manual, not the CASE statement, as they are a bit different.)

Doc Muhlbaier

Duke