2 Replies Latest reply: Jul 13, 2015 3:16 AM by kushal chawda RSS

    Concatenate

      HI

       

        I have 2 tables

             

      [Date][Bank ID][Staff Name][Log Type][Start Time][End Time][Duration (Min)]
      5/5/20151128103Raja Wahab Raja AzhariLogIn/LogOff05/05/2015 09:26:22:24005/05/2015 19:21:23:393595
      5/5/20151128103Raja Wahab Raja AzhariBreakTime05/05/2015 13:41:49:00005/05/2015 14:23:23:00041

       

       

      Table 2:

       

           

      DateBank IDStaff NameTraining Time(Hour)Meeting Time(Hour)
      5/5/20151128103Raja Wahab Raja Azhari00.05
      5/5/20151128103Raja Wahab Raja Azhari00.26
      5/5/20151128103Raja Wahab Raja Azhari00.25

       

       

      Need to concatenate Table 2 with table 1 where Training Time and Meeting Time to be added to the Log type Column and the Respective duration to be added to the Duration Column

       

       

      Expected output after converting the Hours in Table 2 as Mins is as given below.

       

             

      [Date][Bank ID][Staff Name][Log Type][Start Time][End Time][Duration (Min)]
      5/5/20151128103Raja Wahab Raja AzhariLogIn/LogOff05/05/2015 09:26:22:24005/05/2015 19:21:23:393595
      5/5/20151128103Raja Wahab Raja AzhariBreakTime05/05/2015 13:41:49:00005/05/2015 14:23:23:00041
      5/5/20151128103Raja Wahab Raja AzhariTraining Time(Hour) 0
      5/5/20151128103Raja Wahab Raja AzhariMeeting Time(Hour) 33.6
        • Re: Concatenate
          mayilvahanan ramasamy

          Hi

           

          Try like this

           

          Temp:

          CrossTable([Log Type],[Duration (Min)],3)

          LOAD * INLINE [

              Date, Bank ID, Staff Name, Training Time(Hour), Meeting Time(Hour)

              5/5/2015, 1128103, Raja Wahab Raja Azhari, 0, 0.05

              5/5/2015, 1128103, Raja Wahab Raja Azhari, 0, 0.26

              5/5/2015, 1128103, Raja Wahab Raja Azhari, 0, 0.25

          ];

           

           

           

           

          NoConcatenate

          Final:

          Load Date, [Bank ID], [Staff Name], [Log Type],Sum([Duration (Min)]) as [Duration (Min)] Resident Temp

          Group By Date, [Bank ID], [Staff Name], [Log Type];

           

           

          Concatenate(Final)

          LOAD * INLINE [

              Date, Bank ID, Staff Name, "Log Type", "Start Time", "End Time", "Duration (Min)"

              5/5/2015, 1128103, Raja Wahab Raja Azhari, LogIn/LogOff, 05/05/2015 09:26:22:240, 05/05/2015 19:21:23:393, 595

              5/5/2015, 1128103, Raja Wahab Raja Azhari, BreakTime, 05/05/2015 13:41:49:000, 05/05/2015 14:23:23:000, 41

          ];

           

          DROP Table Temp;

          • Re: Concatenate
            kushal chawda

            Table2_temp:

            CrossTable([Log Type],[Duration (Min)],3)

            LOAD * Inline [

            Date,Bank ID, Staff Name, Training Time(Hour), Meeting Time(Hour)

            5/5/2015, 1128103, Raja Wahab Raja Azhari, 0, 0.05

            5/5/2015, 1128103, Raja Wahab Raja Azhari, 0, 0.26

            5/5/2015, 1128103, Raja Wahab Raja Azhari, 0, 0.25 ];

             

             

            Table2:

            NoConcatenate

            LOAD  Date,

            [Bank ID],

            [Staff Name],

            [Log Type],

            sum([Duration (Min)])*60 as [Duration (Min)]

            Resident Table2_temp

            Group by Date,[Bank ID],[Staff Name],[Log Type];

             

             

            DROP Table Table2_temp;

             

             

            Concatenate(Table2)

            LOAD * INLINE [

            Date, Bank ID, Staff Name, Log Type, Start Time, End Time, Duration (Min)

            5/5/2015, 1128103, Raja Wahab Raja Azhari, LogIn/LogOff, 05/05/2015 09:26:22:240, 05/05/2015 19:21:23:393, 595

            5/5/2015, 1128103, Raja Wahab Raja Azhari, BreakTime, 05/05/2015 13:41:49:000, 05/05/2015 14:23:23:000, 41 ];