Only loading latest date for a record from an MS Access Database

[#1]

Hey,

I posted a similar question yesterday, I thought I had it nailed. Turns out the combination of not explaining myself properly and gung-honess when it comes to SQL means I didn't get it down properly.

So here it is:

I had an MS Access Database that I am loading in using ODBC, and using SQL to get the information out.

Specifically I'm focusing on a table that deals with the status on a tenant, and their cases with us.

Here's what I have to load it all in initially:

LOAD
 1 as 'Status_Count',
  Comment,
  `In tenancy`,
  `Input Date` as 'Status_Date',
  `Referral ID`,
  Status;

SQL SELECT　 Comment,
 `In tenancy`,
 `Input Date`,
 `Referral ID`,
 Status
FROM `tbl_Referral_Status`;

Input Date is the date that cases are opened/closed/reopened, the Status is whether they are Closed/Opened/Reopened.

Referral ID is a primary key that links the database together (Each person involved has a unique Ref ID)

One persons case can be opened, closed and reopened multiple times.

So basically I want to only load the latest Status of a tenant (Referral ID). If the case was opened on 26/02/2012, then closed on 04/04/2012, and then reopened on 11/09/12, I only want to load in the details for the 11/09/12 (Reopened) status.

Here's the monstrosity I have so far (That I stole from an SQL forum and doesn't work, but that may be a syntax thing)

LOAD 1 as 'Status_Count',

    `In tenancy`,

    `Input Date` as 'Status_Date',

    `Referral ID`,

    Status;

SQL SELECT      `In tenancy`, `Input Date`, `Referral ID`, Status FROM(

SELECT      `In tenancy`, `Input Date`, `Referral ID`, Status,

RANK() OVER (PARTITION BY `Referral ID` ORDER BY `Input Date` DESC) status_rank

FROM tbl_Referral_Status

) WHERE status_rank = 1;

Which in theory should do what I want but doesn't.

Wow, sorry for the small essay, but this has been annoying me for 2 days now, and I really poorly explained myself yesterday which means you guys won't be able to help.

Should I instead be using a Max(`Input Date`) in the SQL select statement or something? I just don't know at this point.

Thanks for any possible help you can provide.

Joe.

[kaushiknsolanki]

Hi,

     Your earlier post is this.

     http://community.qlik.com/message/328741#328741

     isnt this working?

Regards,

Kaushik Solanki