We have a list of vehicles and need to see which ones are active, retired, or unknown.
We have data from 2010 to 2015. Each year, all vehicles are counted and if they are counted, then they are active.
BUT sometimes a vehicle might show up for 2012, then not show up in 2013. so we need to be able to tell if the unit has been retired, or if it just got missed.
The formula should work like this.
- if a VIN is reported in any given year, record as Active that year
- if a VIN has gone missing for 5 consecutive years, record as Retired
- if a VIN is not reported in a given year, but has been reported within the 5 most recent years, record as Unknown
- if VIN disappears in one year but reappears in any/all of 5 succeeding years, impute "active" status backwards
I am trying to find a simple and elegant way to write up this formula but haven't had much luck. I would like to have it done in the script and not in the tables or a variable.
Any suggestions would be greatly appreciated.
Would you be able to provide some sample data to test it out. I know Stefan might be able to offer help without needing a sample (probably), but for me to move forward, I would need some sample to get this done
I understand, I will try my best to explain
What really matters are the counts. So we can see how many units are being counted each year.
Above is how the data would look in its raw form.
I hope this makes sense, let me know if you need any clarification.
May be this:
CrossTable (Year, Value)
(html, codepage is 1252, embedded labels, table is @2, filters(
Remove(Row, Pos(Top, 6))
Year(Date#(Year, 'YYYY')) as Year,
LOAD AutoNumber(RowNo(), VIN) as Sort,
If(Len(Trim(Flag2)) > 0, Flag2, Flag1) as Flag;
If(Value = 1, 'Active', 'Unknown') as Flag1,
If(VIN = Peek('VIN', -4) and VIN = Peek('VIN', -3) and VIN = Peek('VIN', -2) and VIN = Peek('VIN', -1) and
Peek('Value', -4) = 0 and Peek('Value', -3) = 0 and Peek('Value', -2) = 0 and Peek('Value', -1) = 0 and Value = 0, 'Retired') as Flag2
Order By VIN, Year;
If(Sort = 1 or Len(Trim(Flag3)) = 0, Flag, Flag3) as Flag_Final;
If(Sort > 1 and Flag = 'Unknown' and (VIN = Peek('VIN', -4) and Peek('Flag', -4) = 'Active') or (VIN = Peek('VIN', -3) and Peek('Flag', -3) = 'Active')
or (VIN = Peek('VIN', -2) and Peek('Flag', -2) = 'Active') or (VIN = Peek('VIN', -1) and Peek('Flag', -1) = 'Active'), 'Active') as Flag3
Order By VIN, Year desc;
DROP Tables TempTable, Table, FinalTable;
Yes, I am trying to understand all of the logic. I realize I am still a novice when it comes to scripting, so please bear with me, sorry.
I am having an issue with the FinalTable taking over an hour to run.
I see you created a CrossTable in the first Table, but I think I already have the data you are creating, so I am trying to understand.
Part of it is my fault because I did not explain my table in the script.
Here is my table
So as you can see, I already have the Year (CountYear) defined for each VIN, and then also I created fields for each year. So i don't think the CrossTable would be necessary, would it?
I am not sure how your data looks. Do you have different years as column?
ID, 2015, 2014....
1, 1, 10
or do you have Year field?
ID, Year, Value
1, 2015, 1
1, 2014, 10
If 1st is true, then you will probably need to use CrossTable LOAD. But for 2nd one, you won't have to use the CrossTable LOAD part