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