I am running into some trouble where I need to take the maximum possible value for one field, and take all columns for only that one row within a table. I want to load the request_id, comments and date, but only for the max date, as this field can have multiple values which is skewing calculations.
I need to do this multiple times, because there are different status_id's in the table and then rejoin these rows back to each other to create a new table at the end with just the maximum values together, using the unique identifier request_id.
This is the load script that I currently have;
TEMP_STATUS_HISTORY_Assigned: LOAD status_history_id AS [sh id],
//status_id As [Status ID]
request_id, comments, creation_date
FROM [STATUS_table.qvd](qvd) WHERE status_id = 2;
STATUS_HISTORY_Assigned: LOAD request_id AS [request ID], comments AS [Assigned Comment], date(creation_date) AS [Assigned Date] Resident TEMP_STATUS_HISTORY_Assigned;
inner join (STATUS_HISTORY_Assigned) LOAD [request ID], Date(Max([Assigned Date]),'MM/DD/YYYY') AS [Assigned Date] Resident STATUS_HISTORY_Assigned group by [request ID];
I am then running this same script segment again, but for a different status_id number, which is given a different name also - ie status_id = 5 which is Signed_Off. I then want to join these new fields back to the last table created
left join(STATUS_HISTORY) LOAD [request ID], [Signed Off Comment], [Signed Off Date] Resident STATUS_HISTORY_Signed_Off;
When I run this script for all of my status_ids and try to create one new table at the end, with only the maximum values in order to create calculations on these dates, I am getting an error. It is the error saying 'Assigned Date field not found' when I debug the script. When I run through this script with only one status_id, it runs through fine but once I add in extra different status_ids and different date names, the error resurfaces.
Does anyone know why it is failing to pick up this field?