0

I have created a lookupset below:

=RTRIM(LTRIM(Join(LookupSet(Fields!Fund_Family.Value & Fields!Portfolio_Company_Name.Value,Fields!Fund_Family.Value & Fields!Portfolio_Company_Name.Value,Fields!Security_Type.Value, "DataSet1")," / ").Remove(1,1)))

This seems to work. However, in some particular cases I have two rows, one with the value the other without the value ("") or the Security Column. However, by doing this join the results look like /Security1. However, it should be Security1.There are also scenarios where it picks up Security 1 / / Security 2. It should ignore the " " values.

How can I add in this expression. I know there is an expression for ISNothing, but can this be added here?

a415
  • 359
  • 1
  • 6
  • 23

1 Answers1

1

Solve it in SQL

If this was my project I would rather solve this in the SQL query that produces the data. Since I can't see your SQL query, and it is possible in some circumstances that you can't change the query, I have provided my hacky post-SQL solution.

Hacky Solution

For simplicity, I'm going to use DATA_SET_STRING to represent your code: RTRIM(LTRIM(Join(LookupSet(Fields!Fund_Family.Value & Fields!Portfolio_Company_Name.Value,Fields!Fund_Family.Value & Fields!Portfolio_Company_Name.Value,Fields!Security_Type.Value, "DataSet1")

Something like this will remove empty string names from the list: =REPLACE(DATA_SET_STRING," / "), "/ /", "/").Remove(1,1)))

Empty String & Blank Spaces

If you want to remove blank space names (" ") as well as empty string ("") then I would first convert blank space names to appear just like empty string, then remove the empty strings. Nesting REPLACE statements can accomplish this:

=REPLACE(REPLACE(DATA_SET_STRING," / "), "   ", "  "), "/ /", "/").Remove(1,1)))

Multiple Blanks in a Row

The biggest limitation to this (other than being impossibly difficult to read and maintain) is that it will only remove a finite number of blank names in a row. For instance if you have three blank names in a row your original function DATA_SET_STRING will return "name 1 / / / / name 2". My additional REPLACE functions will result in a final string of "name 1 / / name 2". In order to handle multiple blanks in a row you would have to nest more REPLACE functions.

Something like this: 

=REPLACE(REPLACE(REPLACE(DATA_SET_STRING," / "), "   ", "  "), "/ /", "/"), "/ /", "/").Remove(1,1)))

Although you can solve specific scenarios with this REPLACE approach, it will always be vulnerable to more blanks in a row than you anticipated.

Warnings

Again, I would rather solve this problem by providing a query that filters out the blank and empty string names. However, if you are going to use a hacky approach (I know hacky code can get you out of a jam), here are the weaknesses of the provided solution:

  • Hard to read
  • Hard to maintain
  • Vulnerable to changes in data (too many empty strings in a row)

Remember to code responsibly.

Jesse Potter
  • 827
  • 5
  • 20