Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Get last or first occurrence of data only?

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.

5 Replies
undergrinder
Valued Contributor II

Re: Get last or first occurrence of data only?

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.

Not applicable

Re: Get last or first occurrence of data only?

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.

MVP
MVP

Re: Get last or first occurrence of data only?

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;

kidbank3
Contributor III

Re: Get last or first occurrence of data only?

Rickard,

Have you tried the FirstSortedValue function? 

Mike

Not applicable

Re: Get last or first occurrence of data only?

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