How to make Join with BETWEEN clause in QlikView script

[#1]

Hello,

I'm new to QlikView and looking for help, how to join two tables.

I have two tables one with Activities:

ActivityID	ActivityType	StartDate	FinishDate
1	New	2016-Nov-02 14:00	2016-Nov-04 15:00
1	In Progress	2016-Nov-04 15:01	2016-Nov-05 07:01

And one with TimeDim:

Day Id	Day ..
20161102	Wed
20161103	Thu
20161104	Fri
20161105	Sat

I want to join them - to create fact table showing how much time was spend each day for ActivityType group by ActivityID:

ActivityId	AcivityType	DayId	TimeSpend
1	New	20161102	10
1	New	20161103	24
1	New	20161104	15
1	In Progress	20161104	9
1	In Progress	20161105	7

So I want to make such sql join but in QlikView:

select ActivityId, ActivityType,DayId, calculateTimeSpend

from Activities

join TimeDim

on ( TimeDim.Dayid between Activities.StartDate and Activities.EndDate )

group by ActivityId, ActivityType, DayId

[Anonymous · Accepted solution]

Hi,

I made an application to help you. Need to be very careful to format date. I considered first as text and the format them.

Best regards,

Cosmina

[#3]

Thank you cocosmina for this resolution and fast response.

I put name of month in date to make it easier to read, but i see it make it more difficult.

Thank you again for your help