joining comma separated values with non comma seperated valu

building semantic layer
1

Hi,

In IDT tool i have two fields i want to join.
One field is like;
1
2
3
4
5

And the other is like;
1,1
2
2,1
2,2
2,3
3
4
5,6

Any help please:)

:smiley: :smiley: :smiley: :smiley:

tantetruus (BOB member since 2008-11-11)

2

What data types are the two columns?

3

had to make these string

tantetruus (BOB member since 2008-11-11)

4

OK, so between those two lists, which do you expect to match?

5

probably dont understand your question;
have to match these two.
From the comma seperated table, i have to use only the value on the right, so, if i have these;

1,2
2,2
3
4,1

the values i use are:
2
2
3
1

tantetruus (BOB member since 2008-11-11)

6

Ah, right, so you’re not matching row vs row, you’re matching row vs part row.

This will depend what version of BO you’re on.
As of BI 4.2 SP3, there is a concept of merged variables, which would allow you to create a variable from the comma-separated table. Failing that, you’d probably have to create a derived table in the universe for it to get at that 2 from 1,2.

7

i tried to create a table but since the values include 1 to 10 i have a problem with the 10. If i use the syntax to "clean the comma seperated table like

right([…];1)

then i get a 0 where the 10 should be.

Seem to be missing a few brain cells for this :hb:

tantetruus (BOB member since 2008-11-11)

8

Depending on your database I would try pivoting the table with the CSV values, and then joining to that.

i.e. SQL Server using PIVOT or CROSS APPLY, or Oracle…maybe CONNECT BY, some sort of XML functionality.

Use derived table functionality in IDT to run the pivoting SQL.

YMMV with the performance of this though, as you`re effectively doing ETL on the fly!

ABILtd :uk: (BOB member since 2006-02-08)

9

What you need to do is combine two functions. One, such as the Oracle function INSTR() locates one string within another. Use that to locate the comma. Then use the SUBSTR() function to pull out the characters you want.

SUBSTR(table.col,INSTR(table.col,','),99)

The INSTR() function finds the first comma. If no comma is found, the return value is zero.

Next, SUBSTR() starts at the opening argument (which is the location of the comma) and extracts the rest of the string. I put 99 as an arbitrary value, assuming your string would be less than that.

As I type this I realized an error in the formula, but it’s really close, so I will leave it for you to experiment with. :slight_smile: If you are not using Oracle, determine the equivalent functions for your database.

Dave Rathbun :us: (BOB member since 2002-06-06)

10

thanks Dave,

You mention the function finding the first comma, but is that from the left or the right, since there can be more then one commas?

tantetruus (BOB member since 2008-11-11)

11

What RDBMS are you on?

12

In your example you do not show any data with more than one comma, so I assumed. If you provide incomplete requirements, you get incomplete solutions. :stuck_out_tongue:

If you provide the actual database platform you are working with, as well as a complete set of examples that you’re trying to solve, it would be more likely to get a good result. Or you can take the ideas presented and see if you can stretch to apply them to your situation.

Dave Rathbun :us: (BOB member since 2002-06-06)

13

How’s this for a quick answer…

The field im joining to can be like below:

1,2,8,9

The database is Oracle 12 c

I addes a sheet with the 2 fields of the 2 tables im trying to join in IDT

tantetruus (BOB member since 2008-11-11)