7 Replies Latest reply: Jun 18, 2016 9:44 PM by Sunny Talwar RSS

    Year over Year - Classifying Active, Retired, Unknown Vehicles

    Crystle Stamper

      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.

        • Re: Year over Year - Classifying Active, Retired, Unknown Vehicles
          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

            • Re: Year over Year - Classifying Active, Retired, Unknown Vehicles
              Crystle Stamper
              VIN201520142013201220112010
              A12111110
              B34000001
              C56010011
              D78110111
              Totals231233

              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.

                • Re: Year over Year - Classifying Active, Retired, Unknown Vehicles
                  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;


                  Capture.PNG