Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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
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];
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
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
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.
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 ];