Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
dwqlik82
Contributor III
Contributor III

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 (4)
11 Replies
sunny_talwar

It might be easier to help with a sample where we can test this out.

dwqlik82
Contributor III
Contributor III
Author

Hi Sunny,

Thanks for taking the time to respond, i've had to anonymise the data in the attached so dimensions are all autonumbers rather than text and i've deleted the load script.  The fields are named slightly differently to the example as i tried to make the problem slightly easier in the example than in reality.  the bottom left table in scratchpad sheet is an example of the hard coded expression and i'm using the google timeline extension from qlik branch in case the extension objects in the scratchpad dont render

 

thanks again,

Dale

sunny_talwar

Sorry, I was not able to look at this yesterday. Can you please share the location for the google timeline extension please if it is important for me to look at your issue?

dwqlik82
Contributor III
Contributor III
Author

Hi,

No problem at all, the extension isnt required at all  but for reference its from qlik branch

https://developer.qlik.com/garden/5c38a38f1f9bf60010baa65e

 

The more i think on this the more i think i'll need to change the model to contain a linked table of periodids that overlap each one and do it through the script.

 

thanks again for taking the time.

 

Dale.

 

 

sunny_talwar

I am still trying to figure out the issue here, so I can't really say if you need to change the data model or not. But for right now, I downloaded the extension... but it is saying 'Incomplete visualization'. I didn't make any changes to the app, is the app showing you a chart or do I need to change something to render a result?

dwqlik82
Contributor III
Contributor III
Author

apologies, i'd searched qlik for google timeline and assumed i'd got the correct one, the very similar one below 

https://developer.qlik.com/garden/5c7d3eedcd73a200176e3e5f

is the correct one

sunny_talwar

It still doesn't work for me... it is expecting another dimension it seems like

Capture.png

dwqlik82
Contributor III
Contributor III
Author

how strange, even the documentation on the extension only has 2 dimensions and 3 measures,  either way its just a visualisation of where a given user was:

timeline.PNG

 

essentially what i was trying to do was to select 1 FACL_ID and see all the other FACIL_ID's that had at least 1 overlapping time period for the same location. I initially thought it might be doable with a set of P() functions but my head started to go a bit strange while trying to think of how it would work.

Am increasingly thinking having a separate table with the linked periods in will be the way to go which might avoid doing all the complicated set analysis.

thanks again 

Dale

sunny_talwar

So, if another Facil_ID overlaps for even a second with the selected Facil_ID, you would want to show it?