Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
cocosmina
Contributor II

Re: How to make Join with BETWEEN clause in QlikView script

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

2 Replies
cocosmina
Contributor II

Re: How to make Join with BETWEEN clause in QlikView script

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

Re: How to make Join with BETWEEN clause in QlikView script

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

Community Browser