topic Re: how to get distinct Ticket id based on maximum date in App Development

how to get distinct Ticket id based on maximum date

harish_meda — Fri, 19 Mar 2021 09:22:18 GMT

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

Re: how to get distinct Ticket id based on maximum date

GaryGiles — Fri, 19 Mar 2021 09:08:46 GMT

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)

Re: how to get distinct Ticket id based on maximum date

harish_meda — Fri, 19 Mar 2021 09:19:27 GMT

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;

Re: how to get distinct Ticket id based on maximum date

mschreiber — Mon, 22 Mar 2021 10:31:54 GMT

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;

Re: how to get distinct Ticket id based on maximum date

harish_meda — Mon, 22 Mar 2021 10:50:36 GMT

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

Re: how to get distinct Ticket id based on maximum date

mschreiber — Mon, 22 Mar 2021 11:10:17 GMT

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;