Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
harish_meda
Contributor II
Contributor II

how to get distinct Ticket id based on maximum date

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:

TicketNumberDateResource
41801-01-2021A
41801-04-2021B
66901-01-2021C
66901-06-2021D
TicketNumberDateResource
   
41801-04-2021B
   
66901-06-2020D
2 Solutions

Accepted Solutions
mschreiber
Contributor III
Contributor III

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;

View solution in original post

mschreiber
Contributor III
Contributor III

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;

View solution in original post

5 Replies
GaryGiles
Specialist
Specialist

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
Contributor II
Contributor II
Author

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
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

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

harish_meda_0-1616410105452.png

 

mschreiber
Contributor III
Contributor III

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;