3 Replies Latest reply: Nov 6, 2012 3:42 AM by Stefan Wühl RSS

    intervalmatch with multiple intervals

      Hope someone can help me on this one.  I have table with income intervals, however there are different sets of intervals depending on the number of persons in the household.  For example:

       

      Key:

      Persons in Household/IncomeStart/IncomeEnd/IntervalName

      1/24000/30000/<30%

      1/30001/40000/31-40%

      1/40001/50000/41-50%

      2/25000/31000/<30%

      2/31001/41000/31-40%

      2/41001/51000/41-50%

       

      and so on...

       

      I have another table that has persons in household and incomes:

       

      Population:

      Persons in Household/IncomeLevel

      1/24020

      2/45000

      1/45500

      4/35000

       

      and so on...

       

      how do i script an intervalmatch that will match the IntervalName in the Key table to the IncomeLevel in the Population table, based on persons in household?

       

      Thanks

        • Re: intervalmatch with multiple intervals
          Stefan Wühl

          Look into the extended syntax of intervalmatch, where you can add a [Person in Household] as key to the interval match:

           

          Key:

          LOAD * INLINE [

          Persons in Household/IncomeStart/IncomeEnd/IntervalName

          1/24000/30000/<30%

          1/30001/40000/31-40%

          1/40001/50000/41-50%

          2/25000/31000/<30%

          2/31001/41000/31-40%

          2/41001/51000/41-50%

          ] (delimiter is '/');

           

           

          Population:

          LOAD * INLINE [

          Persons in Household/IncomeLevel

          1/24020

          2/45000

          1/45500

          4/35000

          ] (delimiter is '/');

           

           

          IntervalMatch (IncomeLevel,[Persons in Household]) LOAD IncomeStart, IncomeEnd, [Persons in Household] Resident Key;

            • Re: intervalmatch with multiple intervals

              I'm getting synthetic tables when I do this.  I tried to work around that by joining:

               

              left join (Population)

              IntervalMatch (IncomeLevel,[Persons in Household]) LOAD IncomeStart, IncomeEnd, [Persons in Household] Resident Key;

               


              and then, because the IntervalName field does not carry over, I did another join after the previous:

               

              left join (Population)

              load *

              resident Key;

              drop table Key;

               

              However, there seems to be some data inconsistencies when I do this.  Some fields are missing IntervalNames when they are clearly marked in the Key table.  Anyone know what's going on?

                • Re: intervalmatch with multiple intervals
                  Stefan Wühl

                  The created synthetic table is ok, but if you want to get rid of the synthetic key, I think you can do it like

                   

                  join IntervalMatch (IncomeLevel,[Persons in Household]) LOAD IncomeStart, IncomeEnd, [Persons in Household] Resident Key;

                   

                  Left join (Population) LOAD * resident Key;

                   

                  drop table Key;

                   

                   

                  Not sure why you are missing data when they should have a link to IntervalName. Could you upload some data that demonstrate this issue?