3 Replies Latest reply: Jan 12, 2018 3:31 AM by Julien Le Berre RSS

    Multiplicate specific entries in a table

    Julien Le Berre

      Hello

       

      I would like to transform this table:

       

      customer, rdv, rdv month

      A, 1, march

      B, 0 , -

      C, 1, july

       

      into this table:

       

      customer, month

      A, january

      A, february

      A, march

      A, april

      A, may

      C, may

      C, june

      C, july

      C, august

      C, september

       

      ie one entry in the first table where "rdv"=1, should be transformed into 5 entries in the new table (2 months before, 2 months after the month rdv)

       

      Does anyone have an idea?

       

      Thanks!

        • Re: Multiplicate specific entries in a table
          Julien Le Berre

          Note that I've already found this solution, but I think it could be optimized:

           

          rdvtable:

          NoConcatenate

          load * inline [

          customer,rdv,rdvmonth

          A,1,15/03/2017

          B,0,

          C,1,10/07/2017

          ];

           

          newtable:

          NoConcatenate

           

          load Distinct customer, monthname(rdvmonth) as month

          Resident rdvtable

          where rdv='1';

           

          Concatenate

          load Distinct customer, monthname(AddMonths(rdvmonth,-2)) as month

          Resident rdvtable

          where rdv='1';

           

          Concatenate

          load Distinct customer, monthname(AddMonths(rdvmonth,-1)) as month

          Resident rdvtable

          where rdv='1';

           

          Concatenate

          load Distinct customer, monthname(AddMonths(rdvmonth,1)) as month

          Resident rdvtable

          where rdv='1';

           

          Concatenate

          load Distinct customer, monthname(AddMonths(rdvmonth,2)) as month

          Resident rdvtable

          where rdv='1';

           

          drop tables rdvtable;