topic First or a similar function in QlikView

First or a similar function

Thu, 30 Jul 2009 01:27:26 GMT

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?

First or a similar function

Thu, 30 Jul 2009 01:45:34 GMT

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

First or a similar function

Thu, 30 Jul 2009 01:49:58 GMT

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

First or a similar function

Oleg_Troyansky — Thu, 30 Jul 2009 03:07:57 GMT

Dan,

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

Oleg

First or a similar function

Anonymous — Thu, 30 Jul 2009 03:27:08 GMT

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$]);

First or a similar function

Anonymous — Thu, 30 Jul 2009 03:36:34 GMT

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

First or a similar function

Thu, 30 Jul 2009 20:10:13 GMT

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];