Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
thorsten_wolff
Contributor II
Contributor II

Join

Hello Qlik enthusiasts,

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

SessionIDCheckOUTCheckINLicense_Shortinterval
0000016B1B9FD79306.06.2019 08:48:3406.06.2019 16:19:31MD207:30:57
0000016B1B9FD79306.06.2019 08:52:2506.06.2019 09:30:01KIN00:37:36
0000016B1B9FD79306.06.2019 13:25:5206.06.2019 13:34:10KIN00:08:18
0000016BAA1224BF06.06.2019 08:54:4006.06.2019 18:00:32MD209:05:52

 

The source table looks like this.

SessionIDLog_TypeTimeStampLicense_Short
0000016B1B9FD793Grant06.06.2019 08:48:34MD2
0000016B1B9FD793Grant06.06.2019 08:52:25KIN
0000016BAA1224BFGrant06.06.2019 08:54:40MD2
0000016B1B9FD793Detachment06.06.2019 09:30:01KIN
0000016B1B9FD793Grant06.06.2019 13:25:52KIN
0000016B1B9FD793Detachment06.06.2019 13:34:10KIN
0000016B1B9FD793Detachment06.06.2019 16:19:31MD2
0000016BAA1224BFDetachment06.06.2019 18:00:32MD2

 

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

Labels (2)
1 Solution

Accepted Solutions
jaibau1993
Partner
Partner

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.

View solution in original post

6 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II
Author

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!

 SessionIDLog_TypeStampLicense_Short
A0000016B1B9FD793Grant06.06.2019 08:48MD2
B0000016B1B9FD793Grant06.06.2019 08:52KIN
C0000016BAA1224BFGrant06.06.2019 08:54MD2
B0000016B1B9FD793Detachment06.06.2019 09:30KIN
D0000016B1B9FD793Grant06.06.2019 13:25KIN
D0000016B1B9FD793Detachment06.06.2019 13:34KIN
A0000016B1B9FD793Detachment06.06.2019 16:19MD2
C0000016BAA1224BFDetachment06.06.2019 18:00MD2
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner

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.

View solution in original post

thorsten_wolff
Contributor II
Contributor II
Author

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
Partner
Partner

Glad to help! 🙂