how to subtract two tables in script level

[Srinivas]

Hi All,

I have two tables both tables have same values, and I need to subtract to two tables to create another table in script level.see below tables.

BaseTable:

LOAD * INLINE [

    C_ID, P_ID, B_ID, Name

    1, 101, 50, abd

    2, 102, 51, adf

    3, 103, 52, fgff

    4, 104, 53, egf

    5, 105, 54, egf

    6, 106, 55, edffff

    7, 107, 56, deret

    8, 108, 57, ffrt

    9, 109, 58, gger

    10, 110, 59, errg

    11, 111, 60, erww

    12, 112, 61, c

    13, 113, 62, djj

    14, 114, 63, jnkl

    15, 115, 64, sdi

    16, 116, 65, ljj

    17, 117, 66, knj

    18, 118, 67, kkji

    19, 119, 68, mjh

    20, 120, 69, jjj

];

SecondTable:

LOAD * INLINE [

    C_ID, P_ID, B_ID, Name

    1, 101, 50, abd

    2, 102, 51, adf

    3, 103, 52, fgff

    5, 105, 54, egf

    6, 106, 55, edffff

    15, 115, 64, sdi

    16, 116, 65, ljj

    17, 117, 66, knj

    18, 118, 67, kkji

    20, 120, 69, jjj

];

If we do BaseTable minus(-) SecondTable then we will show to below screenshot way.. this is my output.

Regards,

Munna

[pokassov · Accepted solution]

Hello!

See attach.

If we have to do (table1 - table2) and both tables have the same key field then the approach is

1. load second table - table2. moreother, you can loadn only key field

table2:

load id

from ...

2. load with noconcatenate option the first table - table1 with condition not exists (key field)

table1:

noconcatenate

load

     *

from ...

where not exists(C_ID);

3. drop the second table

drop table table2.

It'll work if you have to substract using only key field not data.

[vardhancse]

Try to use some joins, so that the expected result will come

[petter]

If the condition is that all columns/fields must have the same values then this could be an approach to eliminate "duplicate" rows:

Table:

LOAD AutoNumber( C_ID & '|' & P_ID & '|' & B_ID & '|' & Name  ) AS ExcludeUID INLINE [

    C_ID, P_ID, B_ID, Name

    1, 101, 50, abd

    2, 102, 51, adf

    3, 103, 52, fgff

    5, 105, 54, egf

    6, 106, 55, edffff

    15, 115, 64, sdi

    16, 116, 65, ljj

    17, 117, 66, knj

    18, 118, 67, kkji

    20, 120, 69, jjj

];

BaseTable:

LOAD * , AutoNumber( C_ID & '|' & P_ID & '|' & B_ID  & '|' & Name ) AS UID INLINE [

    C_ID, P_ID, B_ID, Name

    1, 101, 50, abd

    2, 102, 51, adf

    3, 103, 52, fgff

    4, 104, 53, egf

    5, 105, 54, egf

    6, 106, 55, edffff

    7, 107, 56, deret

    8, 108, 57, ffrt

    9, 109, 58, gger

    10, 110, 59, errg

    11, 111, 60, erww

    12, 112, 61, c

    13, 113, 62, djj

    14, 114, 63, jnkl

    15, 115, 64, sdi

    16, 116, 65, ljj

    17, 117, 66, knj

    18, 118, 67, kkji

    19, 119, 68, mjh

    20, 120, 69, jjj

]

WHERE

  Not( Exists( 'ExcludeUID', UID ) );

DROP TABLES Table;