Join

[thorsten_wolff]

Hello Qlik enthusiasts,

I've been trying for a few days now to get the below table as result.

SessionID	CheckOUT	CheckIN	License_Short	interval
0000016B1B9FD793	06.06.2019 08:48:34	06.06.2019 16:19:31	MD2	07:30:57
0000016B1B9FD793	06.06.2019 08:52:25	06.06.2019 09:30:01	KIN	00:37:36
0000016B1B9FD793	06.06.2019 13:25:52	06.06.2019 13:34:10	KIN	00:08:18
0000016BAA1224BF	06.06.2019 08:54:40	06.06.2019 18:00:32	MD2	09:05:52

The source table looks like this.

SessionID	Log_Type	TimeStamp	License_Short
0000016B1B9FD793	Grant	06.06.2019 08:48:34	MD2
0000016B1B9FD793	Grant	06.06.2019 08:52:25	KIN
0000016BAA1224BF	Grant	06.06.2019 08:54:40	MD2
0000016B1B9FD793	Detachment	06.06.2019 09:30:01	KIN
0000016B1B9FD793	Grant	06.06.2019 13:25:52	KIN
0000016B1B9FD793	Detachment	06.06.2019 13:34:10	KIN
0000016B1B9FD793	Detachment	06.06.2019 16:19:31	MD2
0000016BAA1224BF	Detachment	06.06.2019 18:00:32	MD2

I read a lot in the forum and also learned a lot, but I can not get that.

I would be very happy if one of you could lead me to a solution.

Many thanks,

Thorsten

[marcus_sommer]

You could do it within a properly sorted resident-load with interrecord-functions like Peek-or-Previous. Another way seems to be just to join them maybe with something like this:

load SessionID, License_Short, timestamp(min(TimeStamp)) as CheckIN
from Source group by SessionID, License_Short;

join

load SessionID, License_Short, timestamp(max(TimeStamp)) as CheckOUT
from Source group by SessionID, License_Short;

- Marcus

[thorsten_wolff]

Thank you Marcus.

I have tested your proposal. Unfortunately, the result is not the desired one.

The lines with the same letter belong together and form a new line in the result table. This column is not in the loaded data!

	SessionID	Log_Type	Stamp	License_Short
A	0000016B1B9FD793	Grant	06.06.2019 08:48	MD2
B	0000016B1B9FD793	Grant	06.06.2019 08:52	KIN
C	0000016BAA1224BF	Grant	06.06.2019 08:54	MD2
B	0000016B1B9FD793	Detachment	06.06.2019 09:30	KIN
D	0000016B1B9FD793	Grant	06.06.2019 13:25	KIN
D	0000016B1B9FD793	Detachment	06.06.2019 13:34	KIN
A	0000016B1B9FD793	Detachment	06.06.2019 16:19	MD2
C	0000016BAA1224BF	Detachment	06.06.2019 18:00	MD2

[marcus_sommer]

On the first glance it looked that the pairs of records which belong together could be identified uniquely through the used fields. If not you need to extend and/or adjust the fields and logic to get such unique identifying or a join-approach isn't applicable. In this case you could try it with the above mentioned interrecord-functions whereby it will need a similar identifying logic, too.

- Marcus

[jaibau1993 · Accepted solution]

Hi!

For the particular example that you provided the following code works:

SourceTable:
LOAD
     SessionID,
     Log_Type,
     Timestamp#(TimeStamp, 'DD.MM.YYYY hh:mm:ss') as TimeStamp,
     License_Short
;
LOAD * INLINE [
    SessionID, Log_Type, TimeStamp, License_Short
    0000016B1B9FD793, Grant, 06.06.2019 08:48:34, MD2
    0000016B1B9FD793, Grant, 06.06.2019 08:52:25, KIN
    0000016BAA1224BF, Grant, 06.06.2019 08:54:40, MD2
    0000016B1B9FD793, Detachment, 06.06.2019 09:30:01, KIN
    0000016B1B9FD793, Grant, 06.06.2019 13:25:52, KIN
    0000016B1B9FD793, Detachment, 06.06.2019 13:34:10, KIN
    0000016B1B9FD793, Detachment, 06.06.2019 16:19:31, MD2
    0000016BAA1224BF, Detachment, 06.06.2019 18:00:32, MD2
];

FinalTable:
NoConcatenate
LOAD
     *
Where len(Trim(interval))
;
LOAD
     *,
     Interval(if(Log_Type = 'Detachment',TimeStamp - Previous(TimeStamp))) as interval,
     if(Log_Type = 'Detachment',Previous(TimeStamp)) as CheckIN,
     TimeStamp as CheckOUT
;
Load
 *
Resident SourceTable
Order By SessionID, License_Short,TimeStamp,Log_Type;

DROP Table SourceTable;

But a real case may be more complicated so take it as an idea! The output:

0000016B1B9FD793	KIN	06.06.2019 13:25:52	06.06.2019 13:34:10	0:08:18
0000016B1B9FD793	KIN	06.06.2019 08:52:25	06.06.2019 09:30:01	0:37:36
0000016B1B9FD793	MD2	06.06.2019 08:48:34	06.06.2019 16:19:31	7:30:57
0000016BAA1224BF	MD2	06.06.2019 08:54:40	06.06.2019 18:00:32	9:05:52

Regards,

Jaime.

[thorsten_wolff]

Hello Jaime,

you make my day. It is working perfectly.

I was able to read the full license server logs (25000 entries) without any error.

Many thanks,

Thorsten

[jaibau1993]

Glad to help! 🙂