Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Can't find a solution for the following problem:
Have a CSV file with the following information:
SessionID, Time, Action
SessionId is a identifyer, Time is in format hh.mm.ss en Action is "enter session" or"leave session"
Now i want to calculate the time in the session (for each id the time where action is "leave session" minus time where action is "enter session".
Schouldnt be difficult i assume.... Interval function not working with set analysis?
What i have now:
=sum({$<Action={'leave session'}>Time ) - sum({$<Action={'enter session'}>}tijdstip)
It is not giving an error, but i dont reconize the outcome. The format is not the same as the original fields.
How can i calculate this right, or set the format of this calculation (time:hh:mm:ss)?
THanks in advance.
Hello,
I have created a sample data to match with your question. Please check below.
Data:
Table:
Load SessionId, Time#(Time,'hh.mm.ss') as EnterTime ,Action where Action = 'Enter';
Load * Inline [
SessionId,Time, Action
1,09.12.23,Enter
1,10.15.34,Leave
2,08.00.00,Enter
2,9.12.55,Leave
3,06.45.34,Enter
3,11.45.32,Leave
];
Left Join
Table:
Load SessionId, Time#(Time,'hh.mm.ss') as LeaveTime where Action = 'Leave';
Load * Inline [
SessionId,Time, Action
1,09.12.23,Enter
1,10.15.34,Leave
2,08.00.00,Enter
2,9.12.55,Leave
3,06.45.34,Enter
3,11.45.32,Leave
];
Output :
I created another column with LeaveDate and joined it to existing table. Please correct if anything wrong in the understanding.
Thanks,
Ashutosh
Hello,
I have created a sample data to match with your question. Please check below.
Data:
Table:
Load SessionId, Time#(Time,'hh.mm.ss') as EnterTime ,Action where Action = 'Enter';
Load * Inline [
SessionId,Time, Action
1,09.12.23,Enter
1,10.15.34,Leave
2,08.00.00,Enter
2,9.12.55,Leave
3,06.45.34,Enter
3,11.45.32,Leave
];
Left Join
Table:
Load SessionId, Time#(Time,'hh.mm.ss') as LeaveTime where Action = 'Leave';
Load * Inline [
SessionId,Time, Action
1,09.12.23,Enter
1,10.15.34,Leave
2,08.00.00,Enter
2,9.12.55,Leave
3,06.45.34,Enter
3,11.45.32,Leave
];
Output :
I created another column with LeaveDate and joined it to existing table. Please correct if anything wrong in the understanding.
Thanks,
Ashutosh
Hi Ashutosh,
Thank for the solution and your time! I didnt think of solving it in earlier in the process.....
My script is not nice, but i dont get it working good....
Strange enough: the "left join" is not working good (i dont know how that works 🙂 ) Without the left join i have 2 tables and a 3th link-table. All the left en joined lines are there. Left Join added i have 1 table in the end, but only the joined items....
What am i doing wrong?
Thanks again!
Temp:
LOAD [@1:n] as Dataregel,
SubField([@1:n], ', ', 1) as [Session Id],
SubField([@1:n], ', ', 2) as [Participant Id],
SubField([@1:n], ', ', -3) as [Timestamp],
SubField([@1:n], ', ', -2) as [Action],
SubField([@1:n], ', ', -1) as [Role],
SubField([@1:n], ', ', 3) as [Full Name]
FROM
[d:\data\Documenten\Rapportage_teams\AttendeeReport (5).csv]
(fix, utf8, embedded labels);
Temp2:
Load
[Session Id],
[Participant Id],
[Timestamp],
Date#(subfield([Timestamp],' ',1),'DD-MM-YYYY') as Datum,
Time#(subfield([Timestamp],' ',2),'hh:mm:ss') as tijdstip,
[Action],
[Role],
[Full Name]
Resident Temp;
Drop table Temp;
Temp3:
LOAD *,
Timestamp([tijdstip]+'02:00:00','hh:mm:ss') as [Tijdstip +2hrs]
Resident Temp2;
Drop Table Temp2;
Temp4_join:
Load
[Session Id],
[Participant Id],
[Timestamp],
[Tijdstip +2hrs] as [Tijdstip +2hrs_Join],
[Role],
[Action],
[Full Name]
Resident Temp3 where Action = 'Joined';
Left Join
Temp4_left:
Load
[Session Id],
[Participant Id],
[Timestamp],
[Tijdstip +2hrs] as [Tijdstip +2hrs_Left],
[Role],
[Action],
[Full Name]
Resident Temp3 where Action = 'Left';
Drop Table Temp3;
Hello,
Even if you dont join, association will also work.
As per the example, SessionID has to be unique in the data when you break the dataset into 2 (Joined and Left), then only Join will work as expected.
Could you please check that.
Thanks,
Ashutosh
Hi,
I tried to figure out what i am doing wrong. No join will work partially: then i have 2 lines in the tabel and cant calculate the interval.
Left Join gives a result with the "join" time, but no "Left"-time.
Right join opposite....
Tried to make some screenshots.
Hello,
Try following below steps.
1. Take fresh qvd with Session id and Join column. Remove null values from it.
2. Take fresh qvd with 'Left' column. Remove null values from it.
3. Associate or join on 'Session Id'.
Thanks,
Ashutosh