Working with different Calendars and Tables

[Anonymous]

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?

[shubham_singh]

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.

[rwunderlich]

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

http://masterssummit.com

http://qlikviewcookbook.com

[Anonymous]

Thank you very much, I managed to do it with the linktable (like tried before) bit with a Person&Date Key.