topic Re: Filter same users in two tables in QlikView

Filter same users in two tables

Mon, 16 Nov 2015 18:10:54 GMT

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

Re: Filter same users in two tables

Mark_Little — Mon, 16 Nov 2015 18:27:34 GMT

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

Re: Filter same users in two tables

Mon, 16 Nov 2015 18:37:09 GMT

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;

Re: Filter same users in two tables

Mon, 16 Nov 2015 20:11:55 GMT

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;

Re: Filter same users in two tables

Mon, 16 Nov 2015 20:14:15 GMT

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?

Re: Filter same users in two tables

Mark_Little — Mon, 16 Nov 2015 21:14:55 GMT

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