Filtering One Value Based On Another

building reports desktopintelligence
1

Lisa wrote:

I have a key field named Job that is tied to multiple Date fields. Is
there a
way to filter all Job fields that have an specific Date field?

Ex:

Job Date
1111 1/7/98
1/20/98
2/15/98
2222 1/3/98
2/2/98
6666 1/21/98
4444 1/7/98
4/11/98

I want to filter out all jobs work on 1/7/98 resulting in:

Job Date
2222 1/3/98
2/2/98
6666 1/21/98

Any suggestions? Thank in advance for any and all help!

Lisa,
What you will do is create a filter. Instead of selecting the values from the list you will need to define what values to select with a a function. Bring up the filter panel and add the field you want to filter on, in your case date. Then choose the edit button. What you need to do is then create a function to select the values you want to see. You function will look something like this:

= <> ‘1/7/98’

Make sure to include the =. If the date you want to filter out is in a variable you can even use that instead of a constant.

Glenn Fredericks
Data Warehouse Specialist
glenn_fredericks@aal.org
(920) 730-4700 x4236 or 1-800 CALL AAL

Aid Association for Lutherans
4321 N. Ballard Road
Appleton, WI 54919-0001
Visit our Web site at www.aal.org or e-mail us at aalmail@aal.org

AAL… Financial services. Lutheran heritage. A powerful combination.

Listserv Archives (BOB member since 2002-06-25)

2

In a message dated 98-06-01 10:15:11 EDT, you write:

Lisa wrote:

I have a key field named Job that is tied to multiple Date fields. Is
there a
way to filter all Job fields that have an specific Date field?

Ex:

Job Date
1111 1/7/98
1/20/98
2/15/98
2222 1/3/98
2/2/98
6666 1/21/98
4444 1/7/98
4/11/98

I want to filter out all jobs work on 1/7/98 resulting in:

Job Date
2222 1/3/98
2/2/98
6666 1/21/98

Any suggestions? Thank in advance for any and all help!

Lisa,
What you will do is create a filter. Instead of selecting the values from the list you will need to define what values to select with a a function. Bring up the filter panel and add the field you want to filter on, in your case date. Then choose the edit button. What you need to do is then create a function to select the values you want to see. You function will look something like this:

= <> ‘1/7/98’

This is a tricky question. Glenn, in your answer you will remove rows that have a date of ‘1/7/98’, but you won’t remove all of the jobs that have any work done on that date… which if I am reading the question correct is what is desired. Look at the desired result set and you will see that job 4444 has been totally eliminated, not just the row showing work on 1/7/98. Ditto for job 1111.

Lisa, what you are looking for is essentially a sub-query on a report. In other words, show me all of the jobs that are NOT IN LIST of jobs with work on a particular date. I played with this a while and have not come up with a solution for you to use with a Filter. It may be possible, but the more obvious solution to me is to use a Sub-Query instead.

Your initial query could be the same as what you have now. You would add a condition on Job, use the “Not In List” operator, and create a sub-query.

The sub-query would contain the Job as a result object, with a condition for the particular date that you want to eliminate. Something like:

Date Different From ‘1/7/98’

The sub-query returns a list of jobs that don’t have work done on the particular date, which is then used as a list of jobs to report on for the outer query. This solution will eliminate all of the job records that have any work done on a particular date. You could also easily change the date condition from ‘Equal to’ to ‘In List’, ‘Between’, or any other supported operator on the query panel. Use a ‘Prompt’ for the date and you have a fairly flexible report set up.

Any other conditions that you may have on the outer query would also continue to be applied, of course.

The drawback to this solution is that you have to “refresh” the report (requery the database) each time you want to change the date. Depending on the speed of the query this could take a while.

If the sub-query solution does not work for you for one reason or another, please post another question and I will continue trying to work out a Filter solution!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

See you in Orlando in '98!

Listserv Archives (BOB member since 2002-06-25)

3

Dave wrote:

Your initial query could be the same as what you have now. You
would add a
condition on Job, use the “Not In List” operator, and create a
sub-query.

The sub-query would contain the Job as a result object, with a
condition for
the particular date that you want to eliminate. Something like:

Date Different From ‘1/7/98’

Whoops! The date condition on the sub-query should be “Date Equal To ‘1/7/98’”, not “different from”.

The subquery should pull a list of all jobs that occurred on 1/7/98, then the main query should report on all jobs EXCEPT those that the subquery returned.

Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com

This is a tricky question. Glenn, in your answer you will remove rows that have a date of ‘1/7/98’, but you won’t remove all of the jobs that have any work done on that date… which if I am reading the question correct is
what is desired. Look at the desired result set and you will see that job 4444
has been totally eliminated, not just the row showing work on 1/7/98. Ditto
for job 1111.

Lisa, what you are looking for is essentially a sub-query on a report. In other words, show me all of the jobs that are NOT IN LIST of jobs with work on
a particular date. I played with this a while and have not come up with a solution for you to use with a Filter. It may be possible, but the more obvious solution to me is to use a Sub-Query instead.

Your initial query could be the same as what you have now. You would add a condition on Job, use the “Not In List” operator, and create a sub-query.

The sub-query would contain the Job as a result object, with a condition for
the particular date that you want to eliminate. Something like:

Date Different From ‘1/7/98’

The sub-query returns a list of jobs that don’t have work done on the particular date, which is then used as a list of jobs to report on for the outer query. This solution will eliminate all of the job records that have
any work done on a particular date. You could also easily change the date
condition from ‘Equal to’ to ‘In List’, ‘Between’, or any other supported operator on the query panel. Use a ‘Prompt’ for the date and you have a fairly
flexible report set up.

Any other conditions that you may have on the outer query would also continue
to be applied, of course.

The drawback to this solution is that you have to “refresh” the report (requery the database) each time you want to change the date. Depending on the
speed of the query this could take a while.

If the sub-query solution does not work for you for one reason or another, please post another question and I will continue trying to work out a Filter
solution!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

See you in Orlando in '98!

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
For old 3.1 info: Penn: Page not found
Search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 98-06-01 11:40:06 EDT, you write:

The sub-query would contain the Job as a result object, with a
condition for
the particular date that you want to eliminate. Something like:

Date Different From ‘1/7/98’

Whoops! The date condition on the sub-query should be “Date Equal To ‘1/7/98’”, not “different from”.

The subquery should pull a list of all jobs that occurred on 1/7/98, then the main query should report on all jobs EXCEPT those that the subquery returned.

Yes, of course. I started out thinking “Not In List”, and ended up thinking “In List”. Thanks for catching that one Erich.

The sub-query should be one of the following:

Job In List (Sub-Query)
Date Different From ‘1/7/98’

or

Job Not In List (Sub-Query)
Date Equal To ‘1/7/98’

Which solution is more efficient depends on your database structure, the amount of rows returned by the sub-query, and your indexing scheme. Your database administrator can help tune this query.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

See you in Orlando in '98!

Listserv Archives (BOB member since 2002-06-25)

5

I hate to be a nit-picker, Dave, but your first query won’t work. Not given the example that Lisa gave us (with row-id’s added by me):

Ex:

ROW Job Date
1 1111 1/7/98
2 1/20/98
3 2/15/98
4 2222 1/3/98
5 2/2/98
6 6666 1/21/98
7 4444 1/7/98
8 4/11/98

Your first query says, “Report on all jobs where job IS in the sub-query, ‘Select all jobs where date IS NOT 1/7/98.’” The sub-query returns rows 2, 3, 4, 5, 6, and 8 – only rows 1 and 7 are excluded because they fail to meet the criteria. So your list of jobs are 1111, 2222, 6666, and 4444, and thus the main query returns all rows, which is not what was required.

Your second query will work, though. This query says, “Report on all jobs where job IS NOT in the sub-query, ‘Select all jobs where date IS 1/7/98.’” The sub-query only returns rows 1 and 7 because they are the only rows that meet the criteria. So your list of jobs is now just 1111 and 4444. But your main query is going to report on all jobs BUT 1111 and 4444 and only return rows 4, 5, and 6, which is what was required.

Like I said, I’m just nit-picking here. I’m sure you knew all this. Just an honest mistake, right?

Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com

The sub-query should be one of the following:

Job In List (Sub-Query)
Date Different From ‘1/7/98’

or

Job Not In List (Sub-Query)
Date Equal To ‘1/7/98’

Listserv Archives (BOB member since 2002-06-25)

6

Integra Solutions" at Jun 1, 98 12:24:11 pm MIME-Version: 1.0

Dave wrote:

In a message dated 98-06-01 11:40:06 EDT, you write:

The sub-query would contain the Job as a result object, with a
condition for
the particular date that you want to eliminate. Something like:

Date Different From ‘1/7/98’

Whoops! The date condition on the sub-query should be “Date Equal To ‘1/7/98’”, not “different from”.

The subquery should pull a list of all jobs that occurred on 1/7/98, then the main query should report on all jobs EXCEPT those that the subquery returned.

Yes, of course. I started out thinking “Not In List”, and ended up thinking “In List”. Thanks for catching that one Erich.

I thought the point was that jobs could have more than one date, and that you don’t want a job if 1/7/98 is one of the dates for the job.

The sub-query should be one of the following:

Job In List (Sub-Query)
Date Different From ‘1/7/98’

This would eliminate only the jobs whose only date was 1/7/98. It would include jobs that had 1/7/98 but also had other dates.

or

Job Not In List (Sub-Query)
Date Equal To ‘1/7/98’ [stuff deleted]

This, I think, is what is wanted–it will eliminate all the jobs that have 1/7/98 among their dates. The only jobs returned to the document will be jobs that do not have 1/7/98 among their dates.

–Lori

============================================================================ Lorraine A. Ratajczak Internet: ratajczak@isc.upenn.edu
Data Administration voice: (215) 898-5029
University of Pennsylvania fax: (215) 898-0386
3401 Walnut Street, Suite 265C
Philadelphia, PA 19104-6228

Listserv Archives (BOB member since 2002-06-25)

7

In a message dated 98-06-01 13:02:46 EDT, you write:

Like I said, I’m just nit-picking here. I’m sure you knew all this. Just
an honest mistake, right?

Erich Hurst

ARRRRGH! Yes, of course. That will teach me to answer tricky questions before breakfast. Lori caught the same problem…

The sub-query should be one of the following:

Job In List (Sub-Query)
Date Different From ‘1/7/98’

This would eliminate only the jobs whose only date was 1/7/98. It would include jobs that had 1/7/98 but also had other dates.

or

Job Not In List (Sub-Query)
Date Equal To ‘1/7/98’ [stuff deleted]

This, I think, is what is wanted–it will eliminate all the jobs that have 1/7/98 among their dates. The only jobs returned to the document will be jobs that do not have 1/7/98 among their dates.

–Lori

Somewhere along the line I lost track of who initially posted this question… I hope that they stayed long enough to see the final solution!

Thanks folks… I guess that’s my one mistake allotted for the year, eh?

Reminds me of the guy who tried to stop drinking… he cut down to one drink per week. When asked how he justified the number of empty glasses in front of him he replied, “They’re from the next three months.”

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

See you in Orlando in '98!

Listserv Archives (BOB member since 2002-06-25)