Problems with query design

[deleted_user]

hi,

i got a question for one of my queries. This is the structure of my table:

table 1

customer_ID|event|date
1 | A | 20020529
1 | B | 20050403
1 |C | 20040509
2 |C | 20040509
2| B | 20050403
2| A | 20020529

Regarding this dataset my query should only contain the following records:
1|B| 20050403
2| B | 20050403

What might be the settings for the Query-/Filter Designer?

Regards
Chris

[sbb]

Your data example appears to show, for your query steps, the resulting output file will have each unique CUSTOMER_ID, sorted by the DATE (variable / column) in descending order,and output variables CUSTOMER_ID, EVENT and DATE.

Scott Barry
SBBWorks, Inc.

[Cynthia_sas]

Hi:
In addition to Scott's comments, it also looks like your criteria for selection might have something to do either with EVENT or DATE -- what your desire output has in common is that the EVENT for both rows is equal to 'B' and the date for both rows is equal to 20050403.

You are the person who is most familiar with your data. The query that you build will need to use a filter to select the rows that you want. When you build the filter, you have to decide which criteria will get you the rows you want. For example:

Option 1: Filter and Select ALL the rows where event = 'B' and date = '20050403'
OR
Option 2: Filter and Select ALL the rows where event = 'B' (could get ANY date)
OR
Option 3: Filter and Select ALL the rows where date = '20050403' (could get ANY event code)
OR
Option 4: Filter and Select the rows where event = 'B' or date = '20050403'

cynthia

[deleted_user]

Thanks for your comments, but i think i haven't describe my problem exactly, so i try again:

the output should contain that row with the youngest date for every customer

chris Message was edited by: Chris55

[LinusH]

If you want to solve this in Filter and query, you need to create a computed column (max(date)), group by customer_id, and then subset via having (data = maxdate).

/Linus

[deleted_user]

hi linus,

thanks for your reply.
i have implemented that query but i am afraid it doesn't work.
Maybe the format (01012000) of the date-column is the problem?

chris

[deleted_user]

it works ... thank you.

seems my first version had some problems with the having statement!