5 Replies Latest reply: Oct 10, 2017 8:25 PM by jade wind RSS

    How to label records in a transnational table

    jade wind

      Hi everyone,

       

      I need to create table 2 from table 1 below, or convert table 1 to table 3 (basically marking any IDs that have a termination date as "Detached" and any IDs that never have a termination date as "Attached"). I have been struggling with this for a few days. Can anyone help please?

       

      Thanks very much in advance!

       

          table 1:

       

      IDtermination_dateFromTo
      261988-12-201987-03-019999-12-31
      26-1971-12-231976-05-31
      26-1976-06-011987-02-28
      600122011-07-072009-06-302011-05-24
      600122011-07-072011-05-252011-07-07
      60012-2007-02-042007-02-10
      60012-2007-02-112007-02-24
      60012-2007-02-252007-03-10
      60012-2007-03-112007-04-21
      60012-2007-04-222009-06-29
      60012-2011-07-089999-12-31
      4011418-2017-02-132017-02-17
      4011418-2017-02-182017-06-06
      4011418-2017-06-072017-06-22
      4011418-2017-06-232017-06-23
      4011418-2017-06-242017-06-30
      4011418-2017-07-019999-12-31

       

      table 2:

       

        

      IDStatus
      26Detached
      60012Detached
      4011418Attached

       

      table 3:

          

      IDtermination_dateFromToStatus
      261988-12-201987-03-019999-12-31Detached
      26-1971-12-231976-05-31Detached
      26-1976-06-011987-02-28Detached
      600122011-07-072009-06-302011-05-24Detached
      600122011-07-072011-05-252011-07-07Detached
      60012-2007-02-042007-02-10Detached
      60012-2007-02-112007-02-24Detached
      60012-2007-02-252007-03-10Detached
      60012-2007-03-112007-04-21Detached
      60012-2007-04-222009-06-29Detached
      60012-2011-07-089999-12-31Detached
      4011418-2017-02-132017-02-17Attached
      4011418-2017-02-182017-06-06Attached
      4011418-2017-06-072017-06-22Attached
      4011418-2017-06-232017-06-23Attached
      4011418-2017-06-242017-06-30Attached
      4011418-2017-07-019999-12-31Attached
        • Re: How to label records in a transnational table
          Cristina Punga

          Hi,

          When you load table 1, add a calculated field like this

          If(IsNull([termination_date]), 'Attached','Detached')    AS [Status]

          Hope it helps

          Cristina

          • Re: How to label records in a transnational table
            kaan erisen

            Hi,

             

            Script:


            Map:

            mapping load ID,MaxString('Detached') as Status

            group by ID;

            load * Inline [

            ID,termination_date,From,To

            26,'1988-12-20','1987-03-01','9999-12-31'

            26,,'1971-12-23','1976-05-31'

            26,,'1976-06-01','1987-02-28'

            60012,'2011-07-07','2009-06-30','2011-05-24'

            60012,'2011-07-07','2011-05-25','2011-07-07'

            60012,,'2007-02-04','2007-02-10'

            60012,,'2007-02-11','2007-02-24'

            60012,,'2007-02-25','2007-03-10'

            60012,,'2007-03-11','2007-04-21'

            60012,,'2007-04-22','2009-06-29'

            60012,,'2011-07-08','9999-12-31'

            4011418,,'2017-02-13','2017-02-17'

            4011418,,'2017-02-18','2017-06-06'

            4011418,,'2017-06-07','2017-06-22'

            4011418,,'2017-06-23','2017-06-23'

            4011418,,'2017-06-24','2017-06-30'

            4011418,,'2017-07-01','9999-12-31'

            ]

            where len(termination_date)>0;

             

             

            Master:

            load *,

            ApplyMap('Map',ID,'Attached') as Status

            Inline [

            ID,termination_date,From,To

            26,'1988-12-20','1987-03-01','9999-12-31'

            26,,'1971-12-23','1976-05-31'

            26,,'1976-06-01','1987-02-28'

            60012,'2011-07-07','2009-06-30','2011-05-24'

            60012,'2011-07-07','2011-05-25','2011-07-07'

            60012,,'2007-02-04','2007-02-10'

            60012,,'2007-02-11','2007-02-24'

            60012,,'2007-02-25','2007-03-10'

            60012,,'2007-03-11','2007-04-21'

            60012,,'2007-04-22','2009-06-29'

            60012,,'2011-07-08','9999-12-31'

            4011418,,'2017-02-13','2017-02-17'

            4011418,,'2017-02-18','2017-06-06'

            4011418,,'2017-06-07','2017-06-22'

            4011418,,'2017-06-23','2017-06-23'

            4011418,,'2017-06-24','2017-06-30'

            4011418,,'2017-07-01','9999-12-31'

            ];

             

             

            Result:

            percent.png

            • Re: How to label records in a transnational table
              Anand Chouhan

              May be this do this as attached script and found out the termination date and file with flag.

               

              Table1:

              LOAD * INLINE [

                  ID, termination_date, From, To

                  26, 1988-12-20, 1987-03-01, 9999-12-31

                  26, , 1971-12-23, 1976-05-31

                  26, , 1976-06-01, 1987-02-28

                  60012, 2011-07-07, 2009-06-30, 2011-05-24

                  60012, 2011-07-07, 2011-05-25, 2011-07-07

                  60012, , 2007-02-04, 2007-02-10

                  60012, , 2007-02-11, 2007-02-24

                  60012, , 2007-02-25, 2007-03-10

                  60012, , 2007-03-11, 2007-04-21

                  60012, , 2007-04-22, 2009-06-29

                  60012, , 2011-07-08, 9999-12-31

                  4011418,, 2017-02-13, 2017-02-17

                  4011418,, 2017-02-18, 2017-06-06

                  4011418,, 2017-06-07, 2017-06-22

                  4011418,, 2017-06-23, 2017-06-23

                  4011418,, 2017-06-24, 2017-06-30

                  4011418,, 2017-07-01, 9999-12-31

              ];

               

              Left Join(Table1)

              Table2:

              LOAD *,if(Cnt > 1,'Detached','Attached') as StatusNew;

              LOAD Distinct ID,

              Count(DISTINCT termination_date) as Cnt

              Resident Table1 Group By ID;

               

              DROP Field Cnt;

              OP10.PNG