Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vincentparuh13
Partner - Contributor II
Partner - Contributor II

Load Data if it does not exist in another table

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

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Champion III
Partner - Champion III

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
Partner - Contributor II
Partner - Contributor II
Author

Got it Petter, thanks a lot or your help.