where exists

[#1]

Hello

I have two tables that are concatenated and build tableAB:

tableA

tableB

I have three others:

tableC

tableD

tableE

tableC is the parent and is inner joined to both  tableD, tableE

I would like tableC to only contain records found in tableAB (inner join).

I've tried where exists: Load tableC where exists(tableAB.column,tableC.column) but the scripting results report 0 records from tableC, however tableD and tableE still get loaded.

Can someone tell me where Im going wrong and how D and E load while inner joined to a table that apparently has zero records in it?

Thank you.

[maxgro]

Please post the script, thx

[effinty2112]

Try

(this is pseudocode)

C:

Left Keep(AB)

Load

*

From SourceTableC;

AB is a resident table and you want to create a new resident table C. In a Left Keep script like this the data from SourceTableC is reduced down to the rows with that match those in AB in the fields of the same name. Like a Join but the tables remain separate in the data model.

An exists clause is good but only operates on a pair of fields (one in each of the tables that can have different names). Keep can operate on many pairs of like named fields.

In the scenario above a Left Keep will reduce data coming from the source. A Right Keep (not so common) will bring in all records from the source but reduce the resident table to the matching rows. An Inner Keep reduces both to their matching rows.

If you're thinking that keep is just like a join then you're dead right. But there is that big difference - the tables remain separate in the model.

[swuehl]

Are you qualifying your tables?

Then I think you need to take care of the field name in the EXISTS() since the second qualify has not happened when loading in the data:

QUALIFY *;

TableAB:

LOAD * INLINE [

Column

1

2

3

];

TableC:

LOAD * INLINE [

Column

3

5

6

]

WHERE EXISTS(TableAB.Column, Column)

;

[#5]

thank you for your replies, I have something to go with now.