7 Replies Latest reply: Jul 15, 2015 2:18 PM by kushal chawda RSS

    Concatenate

      HI

       

        I have 2 tables

       

      Attendance:

            

      [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

       

       

      Meeting:

       

          

      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
          Nicole Smith

          Code like this will do the trick:

           

          Attendance:
          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
          ];
          
          Meeting:
          CrossTable([Log Type],[Duration (Min)],3)
          LOAD Date, [Bank ID], [Staff Name], sum([Training Time(Hour)]*60) as [Training Time(Hour)], sum([Meeting Time(Hour)]*60) as [Meeting Time(Hour)]
          GROUP BY Date, [Bank ID], [Staff Name];
          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
          ];
          
          CONCATENATE (Attendance)
          LOAD *
          RESIDENT Meeting;
          
          DROP TABLE Meeting;
          

           

          An example file is also attached.

            • Re: Concatenate

              Hi  Nicole,

               

              Attendance:

              LOAD "[RowNum]",

                   "[Date]"  as Date,

                   "[Bank ID]"  as [Bank ID],

                  // "[Staff Name]",

                   "[Log Type]" as [Log Type],

                   "[Start Time]" as Start_Time,

                   "[End Time]" as End_Time,

                   "[Duration (Min)]" as [Duration (Min)]

                  

              FROM

              [C:\Sanction\Data\Attendance\Attendance_and_Break_Time_Log - Apr.xls]

              (txt, unicode, embedded labels, delimiter is '\t', msq);

               

               

              Meeting:

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

              LOAD Date,

                   Team,

                   [Bank ID],

                   [Staff Name],

                   [Training Time(Hour)],

                   [Meeting Time(Hour)],

                   TrainingAttended

              FROM

              [C:\Sanction\Data\Meeting\Cash Apr 1 -15.xls]

              (html, codepage is 1252, embedded labels, table is @1);

               

              CONCATENATE (Attendance)

              LOAD *

              RESIDENT Meeting;

               

               

              DROP TABLE Meeting;

               

               

              I used the above Scripts .

               

              How can i add group by to this as you gave in the example..

               

              Thanks for your reply

                • Re: Concatenate
                  Nicole Smith

                  Meeting:

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

                  LOAD Date,

                       Team,

                       [Bank ID],

                       [Staff Name],

                       sum([Training Time(Hour)]) as [Training Time(Hour)],

                       sum([Meeting Time(Hour)]) as [Meeting Time(Hour)]

                  FROM

                  [C:\Sanction\Data\Meeting\Cash Apr 1 -15.xls]

                  (html, codepage is 1252, embedded labels, table is @1)

                  GROUP BY Team, [Bank ID], [Staff Name];

                    • Re: Concatenate

                      Hi Nicole ,

                       

                      Its throwing error as Invalid expression.

                       

                      Can you do me a favour.. Can you take Meeting and Attendance as 2 excel and load and share the file.

                       

                       

                      Thanks

                        • Re: Concatenate
                          Nicole Smith
                          Attendance:
                          LOAD Date,
                              [Bank ID],
                              [Staff Name],
                              [Log Type],
                              [Start Time],
                              [End Time],
                              [Duration (Min)]
                          FROM
                          Attendance.xls
                          (biff, embedded labels, table is Sheet1$);
                          
                          Meeting:
                          CrossTable([Log Type],[Duration (Min)],3)
                          LOAD Date,
                              [Bank ID],
                              [Staff Name],
                              sum([Training Time(Hour)]*60) as [Training Time(Hour)],
                              sum([Meeting Time(Hour)]*60) as [[Meeting Time(Hour)]
                          FROM
                          Meeting.xls
                          (biff, embedded labels, table is Sheet1$)
                          GROUP BY Date, [Bank ID], [Staff Name];
                          
                          CONCATENATE (Attendance)
                          LOAD *
                          RESIDENT Meeting;
                          
                          DROP TABLE Meeting;
                          

                           

                          Files are attached.

                  • Re: Concatenate
                    Kranthikumar Miryala

                    Try this

                     

                    Attendance:

                    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

                    ];

                     

                    Training:

                    LOAD

                    Date,

                    [Bank ID],

                    [Staff Name],

                    'Training Time(Hour)' as [Log Type],

                    Null() as [Start Time],

                    Null() as [End Time],

                    Sum([Training Time(Hour)])*60 as [Duration (Min)]

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

                    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  

                    ];

                     

                    Meeting:

                    LOAD

                    Date,

                    [Bank ID],

                    [Staff Name],

                    'Meeting Time(Hour)' as [Log Type],

                    Null() as [Start Time],

                    Null() as [End Time],

                    Sum([Meeting Time(Hour)])*60 as [Duration (Min)]

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

                    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  

                    ];

                     

                    Regards,

                    KKR

                    • Re: Concatenate
                      kushal chawda

                      Meeting1:

                      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 ];

                       

                       

                      Meeting:

                      NoConcatenate

                      LOAD  Date,

                      [Bank ID],

                      [Staff Name],

                      [Log Type],

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

                      Resident Meeting1

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

                       

                       

                      DROP Table Meeting1;

                       

                       

                      Concatenate(Meeting1)

                      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 ];