Hi Everyone, I'm looking to learn how to extract the most recent record from a dataset. This is a reporting task that I've done many times in another application (ACL) but I don't understand how to setup and translate the steps for Qlik.
Attached is a sample table containing 2 cases as denoted by the CASE_NUMBER, each line represents an entry for a given month (PERIOD). In the example I want to pull the most recent record, so for CASE # 41 that would be the Period 12 record and for CASE # 12 that would be the PERIOD 9 record. This is a large data set, so anything I could adapt and learn from would be appreciated!!
The script is failing because my source of the data is an OLE DB connection and not the spreadsheet source that I used in my example, sorry for the confusion, I should have stated that................see below: The call you make to Table is not valid since I don't define it. I'm not sure what to substitute. Thanks!
OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=qlickview_user;Data Source=Sched35DEV;Extended Properties="DSN=Sched35DEV;Description=Sched35DEV;UID=qlickview_user;APP=QlikView;WSID=US1900050;DATABASE=ARCPLAN_MET_Sch35";Initial Catalog=ARCPLAN_MET_Sch35] (XPassword is IPCVBJNEYJOcUZQOWRRMGEB);
LOAD CASE_NUMBER&PERIOD AS CasePeriod,
"SI_SCHED_STATUS_ID",
"ARC_OBJECT_NAME",
"ORDER_NUM",
PERIOD,
YR,
AreaCurr,
AreaCode,
LOCATION,
"CASE_NUMBER",
"DATE_REPORTED",
"DATE_INVESTIGATION_STARTED",
"AMOUNT_AT_RISK_USD",
SUBJECT,
"CASE_CONTACT",
PRODUCT,
"CASE_TYPE_AREA",
"COVERAGE_TYPE",
"INT_EXT",
"CASE_DETAILS",
ACTION,
"AMOUNT_RECOVERED_USD",
"AMOUNT_PREVENTED_USD",
"CASE_STATUS",
"DATE_CLOSED",
"NA_FLAG",
"FINAL_FLAG",
"SECURE_FLAG";
SQL SELECT *
FROM "ARCPLAN_MET_Sch35".dbo."User_Input_Sched_35";
