Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 name | Projectname | Startdate project | Enddate project |
---|---|---|---|
Costumer A | Project X | 19-5-2016 | 6-6-2016 |
Costumer A | Project Y | 7-6-2016 | 13-7-2016 |
Costumer B | Project Z | 27-6-2016 | 25-7-2016 |
And I have a table with my spend and other values on datelevel.
Customer name | Date | Spend | Other Value |
---|---|---|---|
Costumer A | 25-5-2016 | 5 | ... |
Costumer A | 1-6-2016 | 6 | ... |
Costumer A | 9-6-2016 | 9 | ... |
Costumer A | 1-7-2016 | 1 | ... |
Costumer A | 29-6-2016 | 5 | ... |
Costumer B | 14-7-2016 | 2 | ... |
Costumer B | 21-7-2016 | 12 | ... |
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
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:
The concept was
You can use IntervalMatch function: IntervalMatch
Does this also work in Qlik Sense?
It says QlikView in the text..
Thanks for your answer,
Isabelle
It will def. work
Thanks, I will try it!
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?
Let me get you a sample
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:
The concept was