Concatenate

[#1]

HI

  I have 2 tables

Attendance:

[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:

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

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/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
5/5/2015	1128103	Raja Wahab Raja Azhari	Training Time(Hour)			0
5/5/2015	1128103	Raja Wahab Raja Azhari	Meeting Time(Hour)			33.6

[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.

[#3]

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

(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

(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

[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

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

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

[#5]

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

[#6]

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

[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.

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