topic Loading Most Recent Record in QlikView

Loading Most Recent Record

gfisch13 — Tue, 01 Mar 2016 17:19:05 GMT

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!!

Re: Loading Most Recent Record

swuehl — Tue, 01 Mar 2016 18:50:06 GMT

Maybe like

TABLE:
LOAD YR&PERIOD as YrPeriod,
PERIOD,
     YR,
     AreaCurr,
     AreaCode,
     LOCATION,
     CASE_NUMBER,
     AMOUNT_AT_RISK_USD,
     SUBJECT,
     CASE_CONTACT,
     PRODUCT,
     CASE_TYPE_AREA,
     COVERAGE_TYPE,
     INT_EXT,
     AMOUNT_RECOVERED_USD,
     AMOUNT_PREVENTED_USD,
     CASE_STATUS,
     DATE_CLOSED,
     NA_FLAG,
     FINAL_FLAG,
     SECURE_FLAG
FROM

(ooxml, embedded labels, table is Sheet1);

INNER JOIN
LOAD
Max(YrPeriod) as YrPeriod,
CASE_NUMBER
Resident TABLE
GROUP BY CASE_NUMBER;

Re: Loading Most Recent Record

gfisch13 — Tue, 01 Mar 2016 19:44:33 GMT

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";

INNER JOIN

LOAD

Max(CasePeriod) as CasePeriod,

CASE_NUMBER

Resident Table

GROUP BY CASE_NUMBER;

Re: Loading Most Recent Record

swuehl — Tue, 01 Mar 2016 20:15:19 GMT

Maybe just use this?

TABLE:

LOAD CASE_NUMBER&PERIOD AS CasePeriod,

"SI_SCHED_STATUS_ID",

"ARC_OBJECT_NAME",

"ORDER_NUM",

PERIOD,

YR,

AreaCurr,

AreaCode,

...

Re: Loading Most Recent Record

gfisch13 — Tue, 01 Mar 2016 20:34:09 GMT

Thank you!! I didn't realize that I could insert "TABLE" in between the OLE DB line and the LOAD statement. Worked perfectly!!! Appreciate the help!