Year over Year - Classifying Active, Retired, Unknown Vehicles

[crystles]

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.

[sunny_talwar]

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

[crystles]

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.

[sunny_talwar]

May be this:

Table:

CrossTable (Year, Value)

LOAD VIN,

    [2015],

    [2014],

    [2013],

    [2012],

    [2011],

    [2010]

FROM

[https://community.qlik.com/thread/221396]

(html, codepage is 1252, embedded labels, table is @2, filters(

Remove(Row, Pos(Top, 6))

));

TempTable:

NoConcatenate

LOAD VIN,

  Year(Date#(Year, 'YYYY')) as Year,

  Value

Resident Table;

FinalTable:

LOAD AutoNumber(RowNo(), VIN) as Sort,

  *,

  If(Len(Trim(Flag2)) > 0, Flag2, Flag1) as Flag;

LOAD *,

  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

Resident TempTable

Order By VIN, Year;

FinalFinalTable:

LOAD *,

  If(Sort = 1 or Len(Trim(Flag3)) = 0, Flag, Flag3) as Flag_Final;

LOAD *,

  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

Resident FinalTable

Order By VIN, Year desc;

DROP Tables TempTable, Table, FinalTable;

[crystles]

That looks like it will work. Let me test it in my code and I will let you know.

Thank you so much for your help!

[sunny_talwar]

I am sure there must be few things which won't work, but something to begin your journey towards a solution

[crystles]

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

POLK:
LOAD VIN,
    CountYear,
    [2015],
    [2014],
    [2013],
    [2012],
    [2011],
    [2010],

FROM
data\POLK_VIN_AllYears.qvd
(qvd)
;

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?

[sunny_talwar]

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