Loading Most Recent Record

[gfisch13]

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

[swuehl]

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;

[gfisch13]

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;

[swuehl · Accepted solution]

Maybe just use this?

TABLE:

LOAD CASE_NUMBER&PERIOD AS CasePeriod,

    "SI_SCHED_STATUS_ID",

    "ARC_OBJECT_NAME",

    "ORDER_NUM",

    PERIOD,

    YR,

    AreaCurr,

    AreaCode,

...

[gfisch13]

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!