Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Mark_Little
Luminary
Luminary

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

View solution in original post

5 Replies
Mark_Little
Luminary
Luminary

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
Author

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
Author

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
Author

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?

Mark_Little
Luminary
Luminary

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