Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
iamscribe
Contributor III
Contributor III

Load only latest record for each unique id

I am trying to load in historical employee location data (which location they worked at and for how long) but I only want to pull in the most recent record for everyone.  Our system flags records as the 'current record' so it's easy to find those, but I want to find records that are no longer current and have a later start date than any other record for that unique ID.  When a person resigns, their location data is retained but their most recent record is no longer flagged as current - we want to only look at current records or the last current record for each PersonID.

RowNumPersonIDStartDateEndDate
CurrentFlagLocationID
111/1/17null1Texas
214/1/1612/31/160Texas
322/1/174/1/170Michigan
421/1/171/31/170Missouri
537/1/17null0Florida
631/1/176/30/171Missouri

Based on this data, we would want to pull rows 1, 3, and 6.

Am I better off trying to flag the data in the load using an if statement? I wasn't sure if this was a scenario to use "Group By PersonID".

Thank you,

Phil

1 Solution

Accepted Solutions
sunny_talwar

May be try this

Table:

LOAD * INLINE [

    RowNum, PersonID, StartDate, EndDate, CurrentFlag, LocationID

    1, 1, 1/1/17, null, 1, Texas

    2, 1, 4/1/16, 12/31/16, 0, Texas

    3, 2, 2/1/17, 4/1/17, 0, Michigan

    4, 2, 1/1/17, 1/31/17, 0, Missouri

    5, 3, 7/1/17, null, 0, Florida

    6, 3, 1/1/17, 6/30/17, 1, Missouri

];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where PersonID <> Previous(PersonID)

Order By PersonID, CurrentFlag desc, EndDate desc;

DROP Table Table;

View solution in original post

4 Replies
sunny_talwar

So are we saying that if CurrentFlag = 1 for a particular PersonID, then pull the row with CurrentFlag = 1, if it isn't then pull the row where StartDate and EndDate are max?

iamscribe
Contributor III
Contributor III
Author

That is correct, we want to pull one record for each PersonID given that criteria.

Thank you,

Phil

sunny_talwar

May be try this

Table:

LOAD * INLINE [

    RowNum, PersonID, StartDate, EndDate, CurrentFlag, LocationID

    1, 1, 1/1/17, null, 1, Texas

    2, 1, 4/1/16, 12/31/16, 0, Texas

    3, 2, 2/1/17, 4/1/17, 0, Michigan

    4, 2, 1/1/17, 1/31/17, 0, Missouri

    5, 3, 7/1/17, null, 0, Florida

    6, 3, 1/1/17, 6/30/17, 1, Missouri

];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where PersonID <> Previous(PersonID)

Order By PersonID, CurrentFlag desc, EndDate desc;

DROP Table Table;

View solution in original post

iamscribe
Contributor III
Contributor III
Author

That seems to have done the trick thank you!  I was trying to use max(StartDate) and FirstSortedValue and such with no luck.  I'll have to look into Previous() and when to use it in loads.

Thanks Sunny!