Load all fields for max date only

Qlik_ULG — Sat, 16 Nov 2024 19:45:12 GMT

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?

Re: Load all fields for max date only

Arthur_Fong — Wed, 06 Nov 2019 00:55:16 GMT

Do you have a sample app?

Re: Load all fields for max date only

Qlik_ULG — Tue, 26 Nov 2019 12:46:46 GMT

Hi Arthur,

Sorry for the delay. This actually resolved by not loading the comments fields, and grouped by the requestID.

Thanks very much.

topic Re: Load all fields for max date only in QlikView

Load all fields for max date only

Re: Load all fields for max date only

Re: Load all fields for max date only