First or a similar function

[#1]

Hi guys,

I have a set of Order data. The fields of interest are Order ID, Screen Number, and Screen status. For each Order ID, there are screen numbers (could be no entries, could be multiple), and each screen number has a Screen Status (screen numbers do not correspond directly to a screen status, meaning that two orders could have screen number 1 triggered, but their screen statuses are not necessarily the same).

What I want to do is get the first Screen status available for an Order, which is the screen status that corresponds to the lowest Screen Number (the first screen number will not necessarily be 1). I'm loading these values from an excel document.

I tried this:

Directory;
First (1)
LOAD [Order ID],
 [Screen Status] as 'Original Status'
FROM Screens.xls (biff, embedded labels, table is [Sheet1$]);

but it only actually loads the first entry in the table, not the first for each Order ID. Anyone know how I can do this?

[#2]

i've attached a sample.

Here, order 6 and 8 have a single screen, with different numbers. 7 has no screen, and 9 has 2 screens. What I'd want the load statement to retrieve for each is: 6 - Proceed, 7 - (blank), 8 - Proceed, 9 - secondary questions

[#3]

for some reason I can't seem to get the attachment to work, here's the table in plain text:

Order ID Screen Number Screen Status
6 1 Proceed
7 *blank* *blank*
8 2 Proceed
9 2 Secondary Questions
9 3 Strong advice to change exam

[Oleg_Troyansky · Accepted solution]

Dan,

take a look at the finction FirstSortedValue - that should give you what you are looking for.

Oleg

[Anonymous]

Dan,
It can be something like this, if I correctly understand what you want:

Screens:
LOAD
 [Order ID],
 min([Screen Number]) as FirsrtScreenNumber
FROM Screens.xls (biff, embedded labels, table is [Sheet1$])
GROUP BY [Order ID];
LEFT JOIN LOAD
 [Order ID],
 [Screen Number]) as FirsrtScreenNumber,
 [Screen Status]
FROM Screens.xls (biff, embedded labels, table is [Sheet1$]);

[Anonymous]

Oh, I didn't know about the FirstOrderDate. It is much easier with it.
Thanks Oleg.

[#7]

Thanks guys, both of your answers were helpful. I used the FirstSortedValue function and also the sample code provided by Michael to use it as I'm relatively new to SQL style commands. Here's what I did:

Directory;
LOAD [Order ID],
 FirstSortedValue([Screen Status], [Screen Number]) as 'Original Status'
FROM Screens.xls (biff, embedded labels, table is [Sheet1$])
GROUP BY [Order ID];