VIN 2015 2014 2013 2012 2011 2010 A12 1 1 1 1 1 0 B34 0 0 0 0 0 1 C56 0 1 0 0 1 1 D78 1 1 0 1 1 1 Totals 2 3 1 2 3 3
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.
- A12 - has been in use since 2011 and has been counted each year since. So it's age is 5
- B34 - has only been counted in 2010, and has not been counted in the 5 years after. So we can assume it is Retired, by 2015. For the years in between, it will be counted as None, since those years are less than 5 years since it's been counted
- C56 - has been counted in 2010 and 2011. But then not counted in 2012 and 2013. Then it shows up again in 2014, but disappears in 2015. So, it will be counted as active in 2010/2011, None in 2013,2012,2015 and Unknown in 2014
- D78 - has been counted in every year except for 2013. We need to be able to assume that it was just missed in 2013, so the None should be changed to Active for year 2013 for this unit
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;
Community_221396.qvw 157.5 K
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?