Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Load all fields for max date only

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?

 

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: Load all fields for max date only

Hi Arthur,

 

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

 

Thanks very much.

View solution in original post

2 Replies
Highlighted
Partner
Partner

Re: Load all fields for max date only

Do you have a sample app?

 

Highlighted
Creator
Creator

Re: Load all fields for max date only

Hi Arthur,

 

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

 

Thanks very much.

View solution in original post