Desktop productivity for business analysts and programmers

Problems with query design

Reply
N/A
Posts: 0

Problems with query design

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Problems with query design

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.
SAS Super FREQ
Posts: 8,718

Re: Problems with query design

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
N/A
Posts: 0

Re: Problems with query design

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
Esteemed Advisor
Posts: 5,198

Re: Problems with query design

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
Data never sleeps
N/A
Posts: 0

Re: Problems with query design

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
N/A
Posts: 0

Re: Problems with query design

it works ... thank you.

seems my first version had some problems with the having statement!
Ask a Question
Discussion stats
  • 6 replies
  • 97 views
  • 0 likes
  • 4 in conversation