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: 
Not applicable

SELECT MIN DATE FROM SCRIPT LOAD

Hi ,

How can i amend my script load to select only min [Appointment Date] for each [Patient ID] ?

Below is my current load script.

---------------

LOAD
[Patient ID],
Date([Appointment Date],'DD-MM-YY') As [Appointment Date],
Date(MonthStart([Appointment Date]), 'MMM-YY') as Month,
[Patient Surname],
[Patient Forename 1],
[Patient DOB],
[Sex Code],
[Address]
FROM
[\Activity from MDS.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);

-------------

Any ideas?

1 Solution

Accepted Solutions
hector
Specialist
Specialist

Hi, try this


t1:
LOAD
[Patient ID],
Date([Appointment Date],'DD-MM-YY') As [Appointment Date],
Date(MonthStart([Appointment Date]), 'MMM-YY') as Month,
[Patient Surname],
[Patient Forename 1],
[Patient DOB],
[Sex Code],
[Address]
FROM
[\Activity from MDS.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
inner join(t1) //so, selecting the min date and joining, will be exclude the other dates
Load
[Patient ID],
MIN( [Appointment Date]) as [Appointment Date]
resident t1
group by
[Patient ID]
;


Rgds

View solution in original post

1 Reply
hector
Specialist
Specialist

Hi, try this


t1:
LOAD
[Patient ID],
Date([Appointment Date],'DD-MM-YY') As [Appointment Date],
Date(MonthStart([Appointment Date]), 'MMM-YY') as Month,
[Patient Surname],
[Patient Forename 1],
[Patient DOB],
[Sex Code],
[Address]
FROM
[\Activity from MDS.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
inner join(t1) //so, selecting the min date and joining, will be exclude the other dates
Load
[Patient ID],
MIN( [Appointment Date]) as [Appointment Date]
resident t1
group by
[Patient ID]
;


Rgds