How to create a new date check status?

[#1]

All,

I am using QlikSense and I want to create Status 2.

It involves searching the same table to see if there was a status check done before the current status check.

If there was not a previous status check in the same table, I would update the Status 2 field as 'No Check Performed'

Account ID	Status	Date	Status 2	Notes
12345	35 Day Expired	3/1/2016	35 Day Expired	If account status = '35 day expired', and the account has a prior status check, then keep Status 2 keeps value of '35 day expired'
12345	Conditional Approval	2/10/2016	Conditional Approval	If status does not equal "expired", then Status 2 keeps same value as Status
68974	35 Day Expired	3/1/2016	No Check Performed	If account status = expired, and the account did not have another status check, then Status 2 becomes 'No Check Performed'

Thanks for your help.

[swuehl]

Maybe like

LOAD

     [Account ID],

     [Status],

     [Date],

     [Account ID] as [AccountIDCheck],

     If( Status <> '35 Day Expired', Status, If(Exists([AccountIDCheck,[Account ID]), Status, 'No Check Performed')) as Status2

RESIDENT YourTable

ORDER BY Date;

DROP FIELD AccountIDCheck;

[#3]

Thanks. I think I forgot to clarify that we are looking for

if Status = '35 Day Expired', see if there were any other Status that existed before for that Account ID.

Basically I want to separate the Account IDs that only had 1 '35 Day expired' status from the population by labeling them ' No Check Performed'

[swuehl]

Maybe I am still misunderstanding your request, but I think these two lines should do what you want:

    [Account ID] as [AccountIDCheck],

     If( Status <> '35 Day Expired', Status, If(Exists([AccountIDCheck,[Account ID]), Status, 'No Check Performed')) as Status2

Have you tried with your data? If it doesn't work for your, could you upload some sample records and your expected result?