topic Re: Concatenate in QlikView

Concatenate

Mon, 13 Jul 2015 06:25:58 GMT

I have 2 tables

[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

Table 2:

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

MayilVahanan — Mon, 13 Jul 2015 06:54:24 GMT

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 — Mon, 13 Jul 2015 07:16:52 GMT

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