Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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'
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?