how to get distinct Ticket id based on maximum date

[harish_meda]

Hi All,

I need to get distinct ticket id based on maximum date .here when i use max(date) in resident table with distinct i am getting two records with two different date  since two resources worked on ticket.

but i want distinct ticket id based on maximum  date.can any one please help me here.

Table:                                                                                                   Result Table:

TicketNumber	Date	Resource
418	01-01-2021	A
418	01-04-2021	B
669	01-01-2021	C
669	01-06-2021	D

TicketNumber	Date	Resource
418	01-04-2021	B
669	01-06-2020	D

[GaryGiles]

Not sure how you are using max(date) in your expression, but if you want the max date, ignoring the dimension, try using:

max(Total date)

[harish_meda]

I am not using this in front end.I need this in backend script.it should ignore the resources and pick distinct Ticket id based on latest date.

example:

Load distinct

TicketNumber,
Max( Date) as Date
Resident Final

Group By TicketNumber;

[mschreiber · Accepted solution]

first load you table in the data modell temporarly:

Tmp_Tickets:
LOAD
TicketNumber,Date,Ressource
From source;

then look for the max-date per ticket

Max_Tickets:
Noconcatenate
Load 
TicketNumber,
MAX(Date) as Date
RESIDENT Tmp_Tickets
GROUP BY TicketNumber;

LEFT JOIN (Max_Tickets)
LOAD
TicketNumber,
Date,
Resource
RESIDENT Tmp_Tickets;

DROP TABLE Tmp_Tickets;

[harish_meda]

Thanks for reply. Same above logic I have done and working fine. but now i need to fix one more scenario .

can you help me on below.

I have one ticket number on same date date but two different resources like below.I need to fetch first record from the table no need to load second record.

Note:My date format as mentioned in table.I don't have timestamp

[mschreiber · Accepted solution]

expand the Tmp_Tickets table with an additional field

Tmp_Tickets:
LOAD
TicketNumber,Date,Ressource,

ROWNO() as rownumber
From source;

after looking for max date an additional load

LEFT JOIN (Max_Tickets)
Load 
TicketNumber,
Min(rownumber) as rownumber
RESIDENT Tmp_Tickets
GROUP BY TicketNumber;

or if it is the smallest resource then

LEFT JOIN (Max_Tickets)
Load 
TicketNumber,
MinString(Ressource) as Ressource
RESIDENT Tmp_Tickets
GROUP BY TicketNumber;

and then the details data

LEFT JOIN (Max_Tickets)
LOAD
TicketNumber,
Date,
Resource
RESIDENT Tmp_Tickets;