BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

joining comma separated values with non comma seperated valu


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
tantetruus
Principal Member
Principal Member



Joined: 11 Nov 2008

Posts: 358



PostPosted: Thu Nov 28, 2019 4:09 am 
Post subject: joining comma separated values with non comma seperated valu

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:)








icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11461
Location: Manchester, UK


flag
PostPosted: Thu Nov 28, 2019 6:15 am 
Post subject: Re: joining comma separated values with non comma seperated

What data types are the two columns?
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
tantetruus
Principal Member
Principal Member



Joined: 11 Nov 2008

Posts: 358



PostPosted: Thu Nov 28, 2019 7:09 am 
Post subject: Re: joining comma separated values with non comma seperated

had to make these string
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11461
Location: Manchester, UK


flag
PostPosted: Fri Nov 29, 2019 5:14 am 
Post subject: Re: joining comma separated values with non comma seperated

OK, so between those two lists, which do you expect to match?
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
tantetruus
Principal Member
Principal Member



Joined: 11 Nov 2008

Posts: 358



PostPosted: Mon Dec 02, 2019 2:11 am 
Post subject: Re: joining comma separated values with non comma seperated

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
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11461
Location: Manchester, UK


flag
PostPosted: Mon Dec 02, 2019 8:46 am 
Post subject: Re: joining comma separated values with non comma seperated

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.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
tantetruus
Principal Member
Principal Member



Joined: 11 Nov 2008

Posts: 358



PostPosted: Mon Dec 02, 2019 9:08 am 
Post subject: Re: joining comma separated values with non comma seperated

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 banghead.gif
Back to top
ABILtd
Forum Enthusiast
Forum Enthusiast



Joined: 08 Feb 2006
ASUG Icon
Posts: 1889


flag
PostPosted: Tue Dec 03, 2019 12:17 pm 
Post subject: Re: joining comma separated values with non comma seperated

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!

_________________
BI and Analytics Presales Consultant
------------------------------------------------------
BOXI R1, R2, R3, R3.1, R4.1, R4.2, CE 8, 8.5, 9, 10, SAP Analytics Cloud, Crystal Reports, Lumira, PowerBI, Tableau, JasperServer, iReport, LogiAnalytics, BIRST, Qlikview, Xcelcius, Netezza, T-SQL, PLSQL, DTS, SSIS, BODI, BODS, Kimble etc....
------------------------------------------------------
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22142
Location: Dallas, Texas


flag
PostPosted: Tue Dec 03, 2019 10:50 pm 
Post subject: Re: joining comma separated values with non comma seperated

tantetruus wrote:
If i use the syntax to "clean the comma seperated table like

right([...];1)

then i get a 0 where the 10 should be.

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.
Code:
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. icon_smile.gif If you are not using Oracle, determine the equivalent functions for your database.

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
tantetruus
Principal Member
Principal Member



Joined: 11 Nov 2008

Posts: 358



PostPosted: Wed Dec 04, 2019 2:04 am 
Post subject: Re: joining comma separated values with non comma seperated

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?
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11461
Location: Manchester, UK


flag
PostPosted: Wed Dec 04, 2019 5:05 am 
Post subject: Re: joining comma separated values with non comma seperated

What RDBMS are you on?
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22142
Location: Dallas, Texas


flag
PostPosted: Tue Dec 10, 2019 9:50 pm 
Post subject: Re: joining comma separated values with non comma seperated

tantetruus wrote:
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?

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. icon_razz.gif

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's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0292 seconds using 17 queries. (SQL 0.0026 Parse 0.0009 Other 0.0257)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo