Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Filter same users in two tables

Dear all,

I want to find the recurrent users in two tables . In order to do this I have two tables with the same fields UserID, FieldA, FieldB, FieldC.

I have tried to do an inner join but it doesn't appear any recurrent and that is not possible.

This is what I have done:

Facts:

LOAD UserID, FieldA, FieldB, FieldC

FROM Table1.xls;

INNER JOIN (Facts)

LOAD UserID, FieldA, FieldB, FieldC

FROM Table2.xls;

The result is a empty Facts:

Could you help me to find them? Another alternative method?

Thanks in advance

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Filter same users in two tables

Hi,

Do you literally just want to find out the recurrent users?

You could try

Facts:

LOAD UserID, FieldA, FieldB, FieldC

FROM Table1.xls;

LEFT JOIN (Facts)

LOAD UserID,

          '1' AS Flag.

FROM Table2.xls;

Then you can check for flag in your set analysis or script?

Mark

5 Replies
Partner
Partner

Re: Filter same users in two tables

Hi,

Do you literally just want to find out the recurrent users?

You could try

Facts:

LOAD UserID, FieldA, FieldB, FieldC

FROM Table1.xls;

LEFT JOIN (Facts)

LOAD UserID,

          '1' AS Flag.

FROM Table2.xls;

Then you can check for flag in your set analysis or script?

Mark

Not applicable

Re: Filter same users in two tables

Try this..

Facts:

LOAD UserID, FieldA, FieldB, FieldC

FROM Table1.xls;

INNER JOIN (Facts)

LOAD UserID,

           FieldA   AS FieldAA,

           FieldB   AS FieldBB,

           FieldC  AS FieldCC

FROM Table2.xls;

Not applicable

Re: Filter same users in two tables

Yes that is the idea.

In the case that I have another Table3.xls. I could also use the same, couldn't I? . The concurrent would be the ones with the flag to 1 is there a way to calculate if he has been two or three times?

Facts:

LOAD UserID, FieldA, FieldB, FieldC

FROM Table1.xls;

LEFT JOIN (Facts)

LOAD UserID,

          '1' AS Flag.

FROM Table2.xls;

LOAD UserID,

          '1' AS Flag.

FROM Table3.xls;

Not applicable

Re: Filter same users in two tables

Great idea thanks.

The only issue is that I need to work later with FieldA, FieldB and FieldC. Is there a way to come back this FieldAAs to the original column?

Partner
Partner

Re: Filter same users in two tables

Hi,

Do you need Field A,B and C from all the tables would there be the same information on each?

Maybe,

Concatenate the tables as FullData

Then

Load

     UserID,

     Count(UserID) as CountofTimes

Resident FullData

Group by UserID;

Or as mentioned in your above reply

LOAD UserID,

          '1' AS Flag.

FROM Table2.xls;

LOAD UserID,

          '1' AS Flag2.

FROM Table3.xls;

Then you can sum(Flag + Flag2) ???

Mark