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: 
ift_isabelle
Partner - Creator III
Partner - Creator III

Value in daterange

Hi,

I'm new to Qlik Sense and I have the following problem:

I have a project start and end date for multiple customers in one table:

Costumer nameProjectnameStartdate projectEnddate project
Costumer AProject X19-5-20166-6-2016
Costumer AProject Y7-6-201613-7-2016
Costumer BProject Z27-6-201625-7-2016

And I have a table with my spend and other values on datelevel.

Customer nameDateSpendOther Value
Costumer A25-5-20165...
Costumer A1-6-20166...
Costumer A

9-6-2016

9...
Costumer A1-7-20161...
Costumer A

29-6-2016

5...
Costumer B14-7-20162...
Costumer B21-7-201612...

Is there a way to check if the date in the second table matches one of the dateranges in the first table and then add the correct projectname to the second table?

Or is it easier to make a Project-Master Calander for the first table?

Thanks,

Isabelle

1 Solution

Accepted Solutions
sunny_talwar

Here, try this:

Table:

LOAD

    "Costumer name" as [Customer name],

    Projectname,

    "Startdate project",

    "Enddate project"

FROM [lib://206822]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD

    "Customer name",

    "Date",

    Spend,

    "Other Value"

FROM [lib://206822]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table)

IntervalMatch(Date, [Customer name])

LOAD [Startdate project],

    [Enddate project],

    [Customer name]

Resident Table;

Output:

Capture.PNG

The concept was

View solution in original post

7 Replies
sunny_talwar

You can use IntervalMatch function: IntervalMatch

ift_isabelle
Partner - Creator III
Partner - Creator III
Author

Does this also work in Qlik Sense?

It says QlikView in the text..

Thanks for your answer,

Isabelle

sunny_talwar

It will def. work

ift_isabelle
Partner - Creator III
Partner - Creator III
Author

Thanks, I will try it!

ift_isabelle
Partner - Creator III
Partner - Creator III
Author

The intervalmatch works, but it doesn't solve my problem.

An extra table is not what I need in my datamodel. I need to integrate the project name in the second table. The intervalmatch just creates a table where I match the date (which can occure multiple times for multiple customers) and I can't match the correct startdate with the correct project.

Is there a way to do integrate the tabels?

sunny_talwar

Let me get you a sample

sunny_talwar

Here, try this:

Table:

LOAD

    "Costumer name" as [Customer name],

    Projectname,

    "Startdate project",

    "Enddate project"

FROM [lib://206822]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD

    "Customer name",

    "Date",

    Spend,

    "Other Value"

FROM [lib://206822]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table)

IntervalMatch(Date, [Customer name])

LOAD [Startdate project],

    [Enddate project],

    [Customer name]

Resident Table;

Output:

Capture.PNG

The concept was