Pick Most Recent Date grouped by Name and Code

[JonesBeach]

Hi,

I am uploading a data set. I want to pick the max "Date Completed" for each "Course Code" by each "Name".

Help is much appreciated!

Thanks!

[Or]

Assuming your chart or table dimensions are Course Code and Name, this would be a case of simply using Date(Max([Date Completed])), with the Date() part being there to format as a date after Max picks out the biggest underlying numeric value.

[JonesBeach]

Thanks for your response Or!

So I dont have to group it by Name or Course Code? 

Just do Date(Max([Date Completed]))

Also the Date column is in (VarChar) format in the sql DataBase for e.g  26-June-2018 16:36:39 UTC -04:00

Would simple Date() be able to fix that?

[anat]

For date part use like date( floor( field), 'dd/mm/yyyy') 

[Or]

Assuming you are using this in a front-end table or chart, you don't have to manually group anything. Everything will automatically be grouped based on the dimensions you add to the table or chart.

Unfortunately this would not be fixable by date(), or date#(), as Qlik won't recognize this as a valid format. You might be able to do this using date#(Subfield([Date Completed],' ',1)), which would tell Qlik to read everything up to the first space and then try to parse that as a date. I'm not sure if having the full month name in there will work well with that, but I'm sure you can figure it out if you have to (if necessary, you could probably further split the subfield by using left(2), right(4), and mid (4,3) to get the day, year, and month components respectively, and then you'd have a 'standard' date format).

[JonesBeach]

Hi Or, I have successfully striped off the time part and now I am left with the date component only. However, 

when I am pulling the new field into my Dashboard table its showing the entire column as null. Could you pls shine some light on that too!! Thanks!

[Or]

Unfortunately, I have no way of knowing why this would be the case without access to the data and code you're using.

[JonesBeach]

Table1:
LOAD*,

Left(Name, Index(Name, '(')-1) as "FULLNAME";

LOAD*,

date#(Subfield([Date Completed],' ',1)),
Date(MAX([Date Completed]), 'DDMMMYYYY') as Date_Comp1;

Load
Name,
"Date Completed",
"Course Codes",
Dept_Manager,
Mid(Dept_Manager, Index(Dept_Manager, '(', 1) + 1, Index(Dept_Manager, ')', -1) - Index(Dept_Manager, '(', 1)-1) as Managers

Resident For_Dates;
Drop Table For_Dates;

This Is my Code and its not working its giving me more than the max Date completed with multiple dates in it rather then just giving me the max date for each name and their corresponding Course Codes. Any help is much appreciated. Thanks!