Resident Load Where exists in resident table

[#1]

I have loaded table that has an id field and for that record it may have been referred by another id.  I need to create a new table from table with the details for the id's where they were a referring id.  I've been trying to use the Where Exists on the resident table but I must be missing something since it always generates zero rows.

Table1:

Load

Id,

Date1,

Date2,

Name

ReferringID

SQL Select * from mySQLSource;

Table2:

Load

Id as ReferringID,

Date1,

Date2,

Name

Resident Table1

where exists (ReferringID,Id);

If I was going to do it in SQL the result I would look for would be like this

Select *

from mySQLSource

where Id in (Select ReferringID from mySQLSource)

but I'm trying not to query that sql source again since it's already been loaded in Qlikview and it has a lot of overhead associated with it.

Is what I'm trying to do even possible, to have the resident sub-select from itself?

Any input appreciated.

Thanks,

Lorna

[#2 · Accepted solution]

Table2:

Load

ReferringID as ID

resident Table1;

Will load a list of those people who were referrers from your first table. (You may need to keep DISTINCT in mind)

You can then do a LEFT JOIN back to Table1 joining on the ID in order to pull ther persons name or whatever other data you want.

[stabben23]

Hi,

where exists(table1,table2) in your case exists(Id, RefferingID) I should also commet away RefferingID in your Table1

//Staffan

[Gysbert_Wassenaar]

Works for me. For example:

X1:
load * inline [
ID, Name, ReferringID
1, a, 2
2, b, 5
3, c, 4
4, d, 6
];

X2:
load ID as ReferringID, Name
Resident X1
where Exists(ReferringID,ID) ;

A keep left should do the same, so that's another thing you can try. In both cases you will end up with a synthetic key unless you concatenate the tables.

That said I'd go for another approach:

Table1:
select Id, Date1, Date2, Name, ReferringID from mySQLSource;

store Table1 into Table1.qvd;
drop table Table1;

Temp:
load ReferringID from Table1.qvd (qvd);

Table1:
load *, if(exists(ReferringID,Id),1,0) as IsReferredID from Table1.qvd (qvd);

drop Table Temp;

This way every ID gets a 1 in a new field IsReferredID if the ID is referred to by another ID. That makes it easy to find/select those ID's.

[#5]

Thanks for the help everyone.  I did end up going with the 'left join' method but I will be able to put the other suggestions in to use in other places.