topic Re: Get last or first occurrence of data only? in App Development

Get last or first occurrence of data only?

Mon, 25 Apr 2016 07:34:34 GMT

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.

Re: Get last or first occurrence of data only?

undergrinder — Mon, 25 Apr 2016 09:33:14 GMT

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.

Re: Get last or first occurrence of data only?

Mon, 25 Apr 2016 11:27:28 GMT

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.

Re: Get last or first occurrence of data only?

swuehl — Mon, 25 Apr 2016 11:35:27 GMT

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;

Re: Get last or first occurrence of data only?

mgranillo — Mon, 25 Apr 2016 12:56:29 GMT

Rickard,

Have you tried the FirstSortedValue function?

Mike

Re: Get last or first occurrence of data only?

Wed, 29 Jun 2016 15:48:15 GMT

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