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: 
dwqlik82
Creator
Creator

Selection of overlapping intervals

Hi,

 

Just wondering if anyone has done something similar trying to show the periods that overlap the ones in a current selection (or a set of periods defined using set analysis in an island table)

i currently have a table similar to the below:

PeriodIDStartDateEndDateLocationUserRowCount
143520.38263888943885.414849537HereRod1
243764.97916666743853.701967593ThereJane1
343783.81458333343857.541805556HereFreddy1
..................

 

What i'd like to be able to do is when a user is selected show all the other periods where the intervals overlap for the same location for that user.

I'd done something as a test using a hard coded periodID that appears to work but that wont scale up to doing multiple periodIDs  (i dont think at least )

Sum({1<PeriodID=
P({1<EndDate={">$(=only({<PeriodID={7510}>}StartDate))"}
, StartDate={"<$(=only({<PeriodID={7510}>}EndDate))"}
, Location={"$(=only({<PeriodID={7510}>}Location))"} >})>}RowCount)

Ideally i'd like to not have to have a separate table with all the intersecting period joined on periodID in it simply due to size (for example just the test example above gives 42 matched periods and there are currently over 92000 periods in the limited data i'm pulling)

 

However if there is no other way then i'll have to look at working out how to do that in script (would be easy in SQL but different data sources...) .

yours hopefully

Dale

Labels (3)
11 Replies
dwqlik82
Creator
Creator
Author

Hi,

Apologies, have been away from my desk for a few days, yep that would be the ask, i'd done it for a given period rather than all periods of a given Facil_ID but due to set analysis only being calculated once per chart my brain fell down a little when trying to nest it so heading toward the idea of doing it in script somehow.

Dale 

dwqlik82
Creator
Creator
Author

Hi,

managed to get some time to look at this again today and did it in script with a duplicate of my main fact table and a table sitting in between to do the joining between the two as i think doing it with a complicated Set Analysis expression is probably just breaking Qlik Sense to fit the need too much

linked.PNG

(the derived start and ends just fill in empty end dates with today's date where a given period is ongoing)

the code to generate the periodlink table is a bit inelegant but seems to work reasonably quickly:

 

TEST:
LOAD ConversionID as CID1
,DerivedStartDTTM as S1
,DerivedEndDTTM as E1
,Location as LocationTest


Resident Conv;

LEFT Join
LOAD ConversionID as CID2
,DerivedStartDTTM as S2
,DerivedEndDTTM as E2
,Location as LocationTest

 

Resident Conv;

NoConcatenate

PeriodLink:
LOAD CID1 as ConversionID
,CID2 as ConversionIDLink
Resident TEST
WHERE CID1 <> CID2 AND S2 < E1 AND E2>S1;

drop table TEST;