Filter same users in two tables

[#1]

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

[Mark_Little · Accepted solution]

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

[#3]

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;

[#4]

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;

[#5]

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]

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