Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

le-haonguyen
New Contributor

How to solve circular reference?

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:

UserIDDatePageCount
320-07-2017155
420-08-2016199
320-07-2017311

B:

UserIdWorkDateWorkDuration
320-07-201703:41:33
420-08-201606:01:13
320-07-201702:51:21

C:

UserIdName
3David
4Michael

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.

Tags (1)
5 Replies
PabloTrevisan
Contributor II

Re: How to solve circular reference?

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:

exep.jpg

Employee
Employee

Re: How to solve circular reference?

You could join the tables

undergrinder
Valued Contributor II

Re: How to solve circular reference?

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.

le-haonguyen
New Contributor

Re: How to solve circular reference?

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:

UserDateWorkdurationPageCount
David20-07-201706:32:54466
Michael20-08-201606:01:13199
PabloTrevisan
Contributor II

Re: How to solve circular reference?

Hi Le-Hao, my example resolves your issue.

Here is the .qvf

leo.jpg

Community Browser