0

Key Columns in MV

I have a scenario where a MV is populated with data in below columns, all three are key columns. After first run of the load ORD# is NULL, but in next load I have data and it could have more than one row for a combination of PO# and PO_Line#. Instead of replacing the NULL Incorta is inserting another line and have 3 rows in final output instead of 2 rows.

PO#   PO_Line#   ORD#

1         1.1              NULL

 

Is there a way to let Incorta know during incremental run, if NULL update row else insert row?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • You mentioned that all three columns are key columns?  Will you have the data like this?

    PO#   PO_Line3  ORD#
    1     1.1       1
    1     1.1       2
    

    In other word, an PO line can point to multiple ORD#.  If not, it seems that the key should be PO# and PO_Line#?

    Like 1
  • Echoing Dylan's question - what logic can you define to tell Incorta which ORD# should replace the NULL?   

    My mantra is "if you can define it, you can make it happen," but if there are not definable criteria you can't get there from here.

    Like 1
  • Thanks Dylan, Dawson for replying.

    The scenario is, Yes, ORD# is key, but initially when PO is created ORD# is NULL after 2-3 weeks the order/orders are created created is source system and need to replace the NULL line with both ORD# number lines.

    Like
  • Tricky - if you can really have:

     

    PO    PO Line    ORD#

    1        1.1            NULL

    1        1.2            NULL

    1        1.1            NULL

     

    And you source system sends an updated  PO - 1   PO Line 1.1  and ORD# 987

     

    there's no way to identify if 987 belongs to row 1, 3, or both.

     

    If you can identify the records by date you could perhaps build an Incorta table w/ two sources - one where ORD# is not NULL and the other where ORD# is NULL, and the order date >= <date_ORD#s_Last_Assigned> and set it to full load so you are constantly refreshing a "complete" set with ORD#s combined with a "provisional" set with NULL ORD#s.

     

    You'd need to make sure there was no overlap or you'd wind up double counting POs and lines. 

     

    Alternatively create two tables ( a NOT NULL table and an "all" table ) and dictate their analytic use depending upon the business case. 

    Like
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 4Replies
  • 17Views
  • 3 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.