Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ift_isabelle
Contributor II

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
MVP
MVP

Re: Value in daterange

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

7 Replies
MVP
MVP

Re: Value in daterange

You can use IntervalMatch function: IntervalMatch

ift_isabelle
Contributor II

Re: Value in daterange

Does this also work in Qlik Sense?

It says QlikView in the text..

Thanks for your answer,

Isabelle

MVP
MVP

Re: Value in daterange

It will def. work

ift_isabelle
Contributor II

Re: Value in daterange

Thanks, I will try it!

ift_isabelle
Contributor II

Re: Value in daterange

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?

MVP
MVP

Re: Value in daterange

Let me get you a sample

MVP
MVP

Re: Value in daterange

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

Community Browser