Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Concatenation using formula issues

  1. #1

    Default Concatenation using formula issues

    Hello all, I am using the following logic to concatenate the strings

    new_field = [street_s]&" "&[city_s]&" "&[zipCode_s]&" "&[country_s]

    If only one of them is null (e.g. [city_s] is null) new_field returns null as well! Doesn't concatenate the other 3 fields. Do you have any idea how to sort this out?

    Thanks.

  2. #2
    Join Date
    Sep 2011
    Posts
    152

    Default

    you can use calculator step to achieve this, use formula A+B+C in calculator.

    let us know if this works

  3. #3

    Default

    I tried. It also returns an empty string if one of the fields is null. Do you have any other idea?
    Last edited by marijamilojevic87@yahoo.c; 10-09-2017 at 09:24 AM.

  4. #4
    Join Date
    Nov 2013
    Posts
    382

    Default

    new_field = if(isna([street_s]);"";[street_s]) &" "& if(isna([city_s]);"";[city_s]) &" "& if(isna([zipCode_s]);"";[zipCode_s])&" "&if(isna([country_s]);"";[country_s])
    Last edited by DepButi; 10-09-2017 at 11:33 AM. Reason: missing )

  5. #5

    Default

    Magic! Thanks!

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.