Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
intervigilium
Contributor III
Contributor III

Set analysis inside a interval function?

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.

1 Solution

Accepted Solutions
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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 :

AshutoshBhumkar_0-1634403589064.png

I created another column with LeaveDate and joined it to existing table. Please correct if anything wrong in the understanding.

Thanks,

Ashutosh

 

View solution in original post

5 Replies
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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 :

AshutoshBhumkar_0-1634403589064.png

I created another column with LeaveDate and joined it to existing table. Please correct if anything wrong in the understanding.

Thanks,

Ashutosh

 

intervigilium
Contributor III
Contributor III
Author

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;

 

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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

intervigilium
Contributor III
Contributor III
Author

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.image.png

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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