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: 
Not applicable

How to make Join with BETWEEN clause in QlikView script

Hello,

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

I have two tables one with Activities:

ActivityIDActivityTypeStartDateFinishDate
1New2016-Nov-02 14:002016-Nov-04 15:00
1In Progress2016-Nov-04 15:012016-Nov-05 07:01

And one with TimeDim:

Day IdDay ..
20161102Wed

20161103

Thu
20161104Fri
20161105Sat

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

ActivityIdAcivityTypeDayIdTimeSpend
1New2016110210
1New2016110324
1New2016110415
1In Progress201611049
1In Progress201611057

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

2 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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