Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Replace NULL value for other value of my query

  1. #1
    Join Date
    Jul 2016
    Posts
    6

    Default Replace NULL value for other value of my query

    Hello.


    I'm using the Merge Join step to join two queries.
    No "Join type" of this step I use the FULL EXTERIOR. Now, I need one of the fields to MERGE that is NULL, receive the value of another field.


    Example:


    I merged query A with query B. The ID adherence field. How they use the complete outer numbers ID of query A are NULL. For these cases, I would like to receive the name of the company. NULL of the query The destination value of the query ID B. How do I do this?


    Thank you!

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I'm trying to make any kind of sense of your post, but failing.

    You have QueryA that is returning:
    RowID | CompanyID | CompanyName | SomeotherColumn
    1 | 1 | Something | Data
    2 | NULL | NewCo | MoreData
    3 | 2 | AnotherCo | Additional Data

    Walk us *SLOWLY* through the rest of what you are doing.

  3. #3
    Join Date
    Jul 2016
    Posts
    6

    Default

    I have in QueryB returning the following:


    RowID2 | CompanyID2 | CompanyName2
    1 | 1 | Something
    2 | 99 | NewCo
    3 | 2 | AnotherCo


    After the Step "Merge Join", using the "Join Step" the FULL OUTER my query looks like this, using CompanyName as the key of QueryA and CompanyName2 as the key of QueryB.


    RowID | CompanyID | CompanyName | SomeotherColumn | RowID2 | CompanyID2 | CompanyName2
    1 | 1 | Something | Data | 1 | 1 | Something
    2 | NULL | NewCo | MoreData | 2 | 99 | NewCo
    3 | 2 | AnotherCo | Additional Data | 3 | 2 | AnotherCo




    After the merge, I need a step that replaces the NULL value of CompanyID with the value of CompanyID2: It would look like this:




    RowID | CompanyID | CompanyName | SomeotherColumn | RowID2 | CompanyID2 | CompanyName2
    1 | 1 | Something | Data | 1 | 1 | Something
    2 | 99 | NewCo | MoreData | 2 | 99 | NewCo
    3 | 2 | AnotherCo | Additional Data | 3 | 2 | AnotherCo


    How can I do this?

  4. #4
    Join Date
    Nov 2009
    Posts
    688

    Default

    You can use a formula for that
    if(isblank([CompanyID]);[CompanyID2];[CompanyID])

  5. #5
    Join Date
    Jul 2016
    Posts
    6

    Default

    Great... Thank you.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You can also use Calculator with the function NVL(A,B) which will create a new column with the value from column A (as defined in Calculator) unless it's null, then it will use value from Column B (As defined in Calculator)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.