topic Re: Classifying in QlikView

Classifying

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I have a list of visits with arrival datetime and departure datetime. I want to count the number of people on site each hour that is from 00:00 to 01:00, 01:00 to 02:00 etc. the issue is someone can visit at 01:30 and leave at 03:15 this means the person should be counted between 01:00 to 02:00 and again between 02:00 to 03:00 and 03:00 to 04:00.

my dimension is calculated dimension as

=TIME(Class(ARR_TIME, 1/24),'hh:mm') & ' - ' & timestamp(class(frac(ARR_TIME)+60/(24*60),60/(24*60)),'h:mm tt')

how best can I achieve this??

Thanks

Re: Classifying

kaushiknsolanki — Mon, 12 Dec 2016 09:44:04 GMT

Hi,

You need the Intervalmatch() function.

Here are some good links.

https://community.qlik.com/thread/93656

IntervalMatch

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/IntervalMatch_(Extended_Syntax).htm

https://www.tutorialspoint.com/qlikview/qlikview_intervalmatch.htm

Regards,

Kaushik Solanki

Re: Classifying

Gysbert_Wassenaar — Mon, 12 Dec 2016 09:47:04 GMT

Then you'll have to split that record up into multiple records, one for each interval. That should be done in the script.

See for example Working between times

Re: Classifying

Mon, 12 Dec 2016 10:12:10 GMT

correct me if I am wrong intervals work here to show how many people came in at a particular time band, but I want to count a person who came in before 7am and is still in at 3pm as in at 10am to 11 am and 11 am to 12 pm

Re: Classifying

Mon, 12 Dec 2016 10:12:16 GMT

Re: Classifying

Gysbert_Wassenaar — Mon, 12 Dec 2016 10:29:46 GMT

Which is why you need to split up the record with one period into records of an hour each

Re: Classifying

Mon, 12 Dec 2016 10:41:25 GMT

I think that's where I am getting lost, I know how to use interval but splitting im not sure what you mean sorry for being slow

Re: Classifying

Gysbert_Wassenaar — Mon, 12 Dec 2016 11:07:44 GMT

Read this discussion and the blog post it links to Working between times