Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I'm new to Qlik Sense and having problems. I currently have 2 Tables I'm working with, which are generated Reports so I can't change them much outside of Qlik Sense.
The Tables look like this:
Table 1:
TaskDate | Person | Duration (hrs) |
---|---|---|
01.04.2016 | Person1 | 1 |
01.04.2016 | Person2 | 2 |
01.04.2016 | Person1 | 3 |
05.05.2016 | Person1 | 4 |
10.05.2016 | Person2 | 5 |
Table 2:
Person | SalaryDate | Salary |
---|---|---|
Person1 | 01.04.2016 | 1000 |
Person2 | 01.04.2016 | 1000 |
Person1 | 01.05.2016 | 1500 |
Person2 | 01.05.2016 | 500 |
My goal is to get a Table looking like this:
Person | sum(Duration) | sum(Salary) |
---|---|---|
So far no Problem. The Problem is to filter for Dates. I can't get a link between "TaskDate" and "SalaryDate", so when I want to filter for April I want to get this:
Person | sum(Duration) | sum(Salary) |
---|---|---|
Person1 | 4 | 1000 |
Person2 | 2 | 1000 |
But with a Filter on "TaskDate" I get this:
Person | sum(Duration) | sum(Salary) |
---|---|---|
Person1 | 4 | 2500 |
Person2 | 2 | 1500 |
I hope you understand what I mean. iI think the problem is, that the Tables are only linked via "Person" and that they have n:m relations.
I tried this approach: Tutorial - Using Common Date Dimensions and Shared Calendars but it didn't work (I think because there are no 1:1 relations..)
Do you have an Idea how to deal with this issue?
It seems that your tables are linked with PersonID, so when you filter "Task Date" in table1 you get all the values of salary corresponding to that person irrespective of date.
To solve this problem you have to link both tables with a key that is made up by a combination of PersonID and Date.
Here is a sample code you could try.
Table1:
LOAD
TaskDate,
Person,
Duration,
Person & '|' & num(TaskDate) as Key
FROM [libe://Conn/Table1.qvd];
Table2:
LOAD
Person,
SalaryDate,
Salary,
Person & '|' & num(SalaryDate) as Key
FROM [libe://Conn/Table2.qvd];
Drop Field Person From Table1; //This is important to avoid syn key.
Num function is used so that dates in both the Key are in same format.
If you want to link them on basis of month instead of date just use MonthSerial in creating Key.
Best way to create uMonthSerial is using ( Year(Date)* 12 ) + num(month(Date)).
Hope it helps.
Perhaps the easiest solution is to concatenate the two tables and then generate a common calendar,
Facts:
LOAD TaskDate,
Person,
[Duration (hrs)],
TaskDate as Date
FROM
[https://community.qlik.com/thread/244113]
(html, codepage is 1252, embedded labels, table is @1);
Concatenate (Facts)
LOAD Person,
SalaryDate,
Salary,
SalaryDate as Date
FROM
[https://community.qlik.com/thread/244113]
(html, codepage is 1252, embedded labels, table is @2);
CALL CalendarFromField('Date', 'CommonCalendar', '');
You can also use a linktable as shown in the tutorial you referenced, but the linkkey will need to be Person&Date,
The attached example shows both the Concatenated and LinkTable solutions,
-Rob
Thank you very much, I managed to do it with the linktable (like tried before) bit with a Person&Date Key.