topic Re: Concatenate in QlikView

Concatenate

Wed, 15 Jul 2015 17:00:42 GMT

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

Re: Concatenate

Nicole-Smith — Wed, 15 Jul 2015 17:14:46 GMT

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

Wed, 15 Jul 2015 17:32:06 GMT

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

Re: Concatenate

Nicole-Smith — Wed, 15 Jul 2015 17:35:18 GMT

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

Re: Concatenate

Wed, 15 Jul 2015 17:36:02 GMT

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

Wed, 15 Jul 2015 17:44:51 GMT

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 — Wed, 15 Jul 2015 18:04:41 GMT

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

Kushal_Chawda — Wed, 15 Jul 2015 18:11:34 GMT

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