Hello,
I am using Qlik Sense Desktop to explore statistics from what could be a website, particularly the connections and disconnections.
What I have is a mysql database in which are stored the logs this way:
id_log, id_user, _date (datestamp format: YYY/MM/DD hh:mm:ss), action (logIn or logOut)
What I am trying to do is to have the duration of the connections of the users, just as if there was an other colum, duration, calculated this way: if action is logIn, look the next rows until the action logOut with the same id_user, and do the _date difference.
(Actually it could be done for the row with a logOut action, but I don't think it matters).
Point is, I can't add this colum to my database, so I thought about two other solutions, but I'm not able to have either of them work.
Could you please help me?
Idea1: Create a field in the load script
Doesn't work basically because I have no idea how to do that. I woud need to request mysql values, make the calculation (the easiest part?) and finally write them into a dimension?
INLINE isn't the way I'm suppose to use I guess, and I can't find anyone else who has tried doing it.
Idea 2: Create the dimension in Qlik Sense
Require the use of the Aggr function because I obviously need to calculate around the _date dimension, I concluded that I should tried to create a measure first so...
Idea 3: Create the measure in Qlik Sense
I ha ve pretty much no idea why it doesn't work the way it should:
I create a table with _date, id_user and action (at this point it look likes the mysql table) then add a measure _duration whose formula is if(action='singIn', 1) -works fine- then puts if(action='singIn', Min(date_log)) (I'm progressing step by step) which results in _duration and _date beeing equal.
So I try if(action='singIn', Min({1} date_log)), which give the exact same result, which I don't understand...
Isn't Min({1} date_log) supposed to display the minimum value of date_log, no matter the conditions ???
-----------------------------------------------------------------------------------------------------------------------------------------------------
Since I am asking questions, here is one that in my opinion relates with my original problem but can be explained with few words:
If I have a dimension as below, how do I get the result I'm looking for ? :
| _dimension | Min( _dimension) | Min({1} _dimension) (Why is it this ?) | actuel minimum (what I thought would result) | next value for even numbers, value for odd numbers (how could I do that ?) | next even value for even numbers, value for odd numbers (more difficult ?) |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 1 | 5 | 28 |
| 5 | 5 | 5 | 1 | 5 | 5 |
| 17 | 17 | 17 | 1 | 17 | 17 |
| 28 | 28 | 28 | 1 | 81 | 122 |
| 81 | 81 | 81 | 1 | 81 | 81 |
| 122 | 122 | 122 | 1 | 124 | 124 |
| 124 | 124 | 124 | 1 | 127 | NULL |
| 127 | 127 | 127 | 1 | 127 | 127 |
Can someone explain me what I don't get?
