Get last or first occurrence of data only?

[#1]

Hi, I am doing analysis of data that is basically a daily download containing a snapshot with all open cases from a ticket-system.

I some cases I am interested in only the first occurrence of that ticket (or the last) - ie. to do graphs on new tickets created (date) any ideas on how I could do that?

also. any idea how to find out trend on tickets being closed (that would mean identifying rows that "disappeared" in today's loading compared to yesterday's loading.

[undergrinder]

Hi Rickard,

Use autonumber function to create a flag for first occurence.

I assume you have a TimeStamp and a Ticket ID.

tmp_ticket:

Load

*

,autonumber(TimeStamp,TicketId) as Occurence

Resident YourTable;

ticket:

Load * Resident tmp_ticket Where Occurence = 1;

drop table tmp_ticket;

The autonumber above create an unique key for TimeStamp by Ticket ID. The first occerence get 1, the second 2 and so on. If you have duplicated timestamp and ticket id, it won't help.

G.

[#3]

Seems this doesn't work when I load a number of excel files in the same load (\lib\something*.xls)

(one file is a snabshot if that days opened tickets) - every file seems to nullify the counter.

[swuehl]

I assume your files contain TicketID and a snapshot date (which you may derive from filename()?):

I also assume that SnapShotDate is interpreted as a date, i.e. it shows a numeric representation after loading in (use

a correct default date format or apply interpretation functions if needed):

TICKETS:

LOAD

     TicketID,

     SnapShotDate

FROM [\lib\something*.xls]

(..);

OPEN:

LEFT JOIN

LOAD TicketID,

          Min(SnapShotDate) as SnapShotDate,

          1 as OpenFlag

RESIDENT TICKETS

GROUP BY TicketID;

CLOSED:

LEFT JOIN

LOAD TicketID,

          If(Max(SnapShotDate) < Today(), Max(SnapShotDate) )  as SnapShotDate,

          1 as ClosedFlag

RESIDENT TICKETS

GROUP BY TicketID;

[mgranillo]

Rickard,

Have you tried the FirstSortedValue function? 

Mike

[#6]

Hmm, that looks interesting . let me give it a try.