How to solve circular reference?

[Anonymous]

Hi guys,

im new to Qlik and i got a problem i can't solve.

i got two fact tables  and one meta table

A:

UserID	Date	PageCount
3	20-07-2017	155
4	20-08-2016	199
3	20-07-2017	311

B:

UserId	WorkDate	WorkDuration
3	20-07-2017	03:41:33
4	20-08-2016	06:01:13
3	20-07-2017	02:51:21

C:

UserId	Name
3	David
4	Michael

now i want to see how much pages a user do on a specific date. its no problem when i filter on both date and workdate but when i want to use only one date filter i always get circular references because userid's are also linked already.

[PabloTrevisan]

Hi Le-Hao, try to make a key, UserID&'-'&Date on table A and UserID&'-'&WorkDate on table B.

The results of SUM = 665

A:

LOAD

    "UserID"&'-'&"Date" as key,

     UserID,

    PageCount

FROM [lib://doc/user1.xlsx]

(ooxml, embedded labels, table is Planilha1);

B:

LOAD

    "UserID"&'-'&WorkDate as key,

    TimeStamp(WorkDuration,'hh:mm:ss') as WorkDuration

FROM [lib://doc/user12.xlsx]

(ooxml, embedded labels, table is Planilha1);

C:

LOAD

    "UserID",

    Name

FROM [lib://doc/user123.xlsx]

(ooxml, embedded labels, table is Planilha1);

Result:

[Lisa_P]

You could join the tables

[undergrinder]

You're right.

I don't see why need to be the A, B as two separate table .

A:

Load

     UserID,

     Date,

     PageCount

From [...];

Left join(A)

Load

     UserID,

     WorkDate as Date

From [....];

G.

[Anonymous]

Hey thanks for your help guys but i think i wasnt specific enough the goal is to see what user did how many pages on a specific date inclusive their workduration in my example i would have something like this:

User	Date	Workduration	PageCount
David	20-07-2017	06:32:54	466
Michael	20-08-2016	06:01:13	199

[PabloTrevisan]

Hi Le-Hao, my example resolves your issue.

Here is the .qvf