Hi,
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];
drop table TEMP_STATUS_HISTORY_Assigned;
STATUS_HISTORY:
LOAD
[request ID],
[Assigned Comment],
[Assigned Date]
Resident
STATUS_HISTORY_Assigned;
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?
