Load Data if it does not exist in another table

[vincentparuh13]

Currently I have two tables M_InoutLine and M_MatchInv. Both have M_InoutLine_ID and i want to load rows from M_InoutLine that doesnt exist in M_MatchInv.  I have tried this script but to no avail

m_inoutline:

LOAD

m_inoutline_id,

    m_inoutline.m_inout_id as m_inout_id,

    m_inoutline.m_product_id as m_product_id,

    m_inoutline.movementqty as movementqty,

    m_inoutline.c_orderline_id as c_orderline_id

FROM [m_inoutline.qvd]

(qvd);

m_matchinv:

LOAD

    m_matchinv_id,

    m_matchinv.m_inoutline_id

FROM [m_matchinv.qvd]

(qvd);

concatenate(m_matchinv)

LOAD m_inoutline_id as m_matchinv.m_inoutline_id,

movementqty

resident m_inoutline

where not exists([m_matchinv.m_inoutline_id],m_inoutline_id);

Any help would be greatly appreciated

[petter · Accepted solution]

If you switch the order of loading of the two tables you can test for a match or no-match:

m_matchinv:

LOAD

    m_matchinv_id,

    m_matchinv.m_inoutline_id

FROM [m_matchinv.qvd]

(qvd);

m_inoutline:

LOAD

    m_inoutline_id,

    m_inoutline.m_inout_id as m_inout_id,

    m_inoutline.m_product_id as m_product_id,

    m_inoutline.movementqty as movementqty,

    m_inoutline.c_orderline_id as c_orderline_id

FROM [m_inoutline.qvd] (qvd);

WHERE

   Not( Exists( m_matchinv , m_inoutline_id ));

In your original code it seems like you have an Exists clause that will not work. I think it should look like this:

     where not(exists( m_matchinv_id,m_inoutline_id));

[vincentparuh13]

Petter ,

Thankyou , it actually works.

Do you mind to elaborate on why if i change the order of loading two tables would have different result?

Was it because on the first field that is evaluated by EXISTS evaluates all fields above it/ loaded before?

[petter]

Exists use as the first parameter an in-memory field and check against all the values that has already been loaded into that field against the single value of the second parameter for the current row being loaded.

[vincentparuh13]

Got it Petter, thanks a lot or your help.