where not exists

[ttal7878]

HI,

In the example attached, I try to use the where not exists

Because I do not have a unique field in my table,  when I load the model it causes me to lose data,

only one row with ID = D is added to the main table

How can I load Table A and add the data from Table B but only the ID's that do not exist in Table A?

[sunny_talwar]

May be try this

A:

LOAD * INLINE [

    ID,DEP,Name

    A,red

    B,yellow

    C,black

];

B:

NoConcatenate

LOAD ID as ID1,

DEP,

Name;

LOAD * INLINE [

  ID,DEP,Name

    A,green

    D,grey

    D,white

];

Right Join (B)

LOAD ID1

Resident B

Where not Exists(ID, ID1);

Concatenate(A)

LOAD ID1 as ID,

DEP,

Name

Resident B;

DROP Table B;

[johnca]

Sunny, I modified it a bit...(Your code seemed to keep all rows instead of just the 2 where ID = D)

A:

LOAD * INLINE [

    ID,DEP,Name

    A,red

    B,yellow

    C,black

];

B:

NoConcatenate

LOAD ID as ID1,

DEP,

Name;

LOAD * INLINE [

   ID,DEP,Name

    A,green

    D,grey

    D,white

];

C:

NoConcatenate

Load ID1 as ID,DEP,Name;

LOAD ID1,DEP,Name

Resident B

Where not Exists(ID, ID1);

Drop Tables A,B;

[sunny_talwar]

I think you might have misunderstood the problem. OP mentioned

"How can I load Table A and add the data from Table B but only the ID's that do not exist in Table A?" You don't have table A data anymore. I don't think this is the requirement, unless I have not understood the issue .

My code gave this

All of Table A and then only D from Table B.

[johnca]

Perhaps. I was under the impression she wanted just the ID's that existed in B that were NOT in A. This means only ID = D. My modification of your code gave this:

[johnca]

Dang, I just reread the initial post. You're right Sunny. <sigh>

[ttal7878]

My intention is just as Sunny has described.

Thanks guys for your help, it helped me a lot

[sunny_talwar]

[ttal7878]

Hey Sunny,

In your reply, when I look at the Table Viewer in the preview dialog,

I see that ID = 'D' has doubled itself, attached a screenshot

[sunny_talwar · Accepted solution]

My bad, try this

A:

LOAD * INLINE [

    ID,DEP,Name

    A,red

    B,yellow

    C,black

];

B:

NoConcatenate

LOAD ID as ID1,

DEP,

Name;

LOAD * INLINE [

  ID,DEP,Name

    A,green

    D,grey

    D,white

];

Right Join (B)

LOAD DISTINCT ID1

Resident B

Where not Exists(ID, ID1);

Concatenate(A)

LOAD ID1 as ID,

DEP,

Name

Resident B;

DROP Table B;