How to get record from a table not present in other table

[#1]

Hi,

I have two tables. To simplify, table A and table B. The two tables have a field to make a join between them, for example Join_Field.

I want to load data from table 1, and keep only record not present in table B, using Join_Field as criteria.

Example:

Table A

---------------------------------------

Field A1,  Field A2,  Join_Field

A               B              1

C               D              2

Table B

-----------------------------------------

Field B1,  Field B2,  Join Field

W               X               1

Y                Z                1

RESULT

----------------------------------------------------------------------------------

Field A1,   Field  A2,   Join Field         Field B1          Field B2

C                  D                2                    blank              blank

If fields B1 and B2 cannot be get in result, it does not matter (I can fill manually in a later step)

How can I do it??

Regards.

[MayilVahanan]

HI

Try with

Not Exists() Function

like

Load * from tableA where not exsists (JoinField, JoinFieldofB);

Edit:

I think not exists does not solve for your requirement, try like this

Test:

LOAD * inline [

FieldA1,  FieldA2,  JoinField

A,B,1

C,D,2

];

Outer Join(Test)

LOAD * Inline [

FieldB1,FieldB2,JoinField

W,X,1

Y,Z,1

];

NoConcatenate

LOAD * Resident Test Where IsNull(FieldB2);

DROP Table Test;

Hope that helps

[#3 · Accepted solution]

Pleas refer the following application

[#4]

Hi,

PFA

[#5]

Hi Venkat,

Thanks. I have tried with your example and Works, but I have a problema when trying with my actual tables.

The problem is because join field has not the same name in both tables.

How do I have to modify your solution if fields have the following names??

Table A

Field A1, Field A2,  LOCATOR

Table B

Field B1, Field B2, LOCALIZADOR

I have tried several ways (using AS LOCATOR, and so on), but field is not recognized.

Thanks a lot for your help.

Regards

[#6]

Sorry again.

I have solved this problem, but I have another one. I get the Table with the results, following your instructions. But when I also add to the script the load of origin tables (I also need to keep origin tables with a join), qlikview hangs.

In fact I need:

- Records of table A present in table B, filtered by table B criteria

- Record of table A not present in table B (your solution)

How can I have both in same script?

[#7]

Hi again,

I have solved. I forget an unqualify sentence before join, and several syn were being created.

Regards