Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
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;

View solution in original post

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;

View solution in original post