Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

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
MVP
MVP

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

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
MVP
MVP

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

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

Partner
Partner

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

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?

MVP
MVP

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

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.

Partner
Partner

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

Got it Petter, thanks a lot or your help.