topic Re: Load Data if it does not exist in another table in App Development

Load Data if it does not exist in another table

vincentparuh13 — Tue, 16 Oct 2018 08:02:49 GMT

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

Re: Load Data if it does not exist in another table

petter — Wed, 17 Oct 2018 06:10:02 GMT

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));

Re: Load Data if it does not exist in another table

vincentparuh13 — Wed, 17 Oct 2018 11:18:04 GMT

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?

Re: Load Data if it does not exist in another table

petter — Wed, 17 Oct 2018 12:01:45 GMT

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.

Re: Load Data if it does not exist in another table

vincentparuh13 — Thu, 18 Oct 2018 06:16:05 GMT

Got it Petter, thanks a lot or your help.