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

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

Concatenate Year Month and Year Week


 
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
s7jyothi
Principal Member
Principal Member



Joined: 07 Sep 2008

Posts: 167



PostPosted: Wed Feb 04, 2009 2:16 pm 
Post subject: Concatenate Year Month and Year Week

HI,
I have objects in the universe which are year, month and week. I have to concatenate Year and Month and Year and Week objects to make it to six digit. The concatenated object should be 200906 and I have to add a zero before the month so that it becomes a six digit figure like 200906, 200907 and eliminate zero if it is 10, 11 and 12 months so that it would be already a six digit number like 200910, 200911 and 200912 .
Is it possible to do this concatenation and do I have to use if then else or case statments.
Requesting for help and suggestions.
Thanks
Back to top
BO_Chief
Forum Fanatic
Forum Fanatic



Joined: 06 Jun 2004
ASUG Icon
Posts: 5532
Location: Somewhere on God's Land.


flag
PostPosted: Wed Feb 04, 2009 3:04 pm 
Post subject: Re: Concatenate Year Month and Year Week

What database do you use ?

Provide the correct format mask for the date format for your database...

Like in Oracle, you can use TO_CHAR function with YYYYMM format mask... and create an object in the universe... it will return 6 digits..

_________________
help us help you! make sure your post has the following elements:
Does it include BO version, Database, an error, a problem, a SQL for object or Condition ?
Does it include some sample data what you have?
Does it include any code you already tried ? (working or not..)
Did you explain what you want for results?
If any of those elements are missing,chances are you didn't post enough information for us to help you!


Last edited by BO_Chief on Wed Feb 04, 2009 3:32 pm, edited 1 time in total
Back to top
jsanzone
Forum Associate
Forum Associate



Joined: 12 Sep 2006

Posts: 936
Location: Washington, DC


flag
PostPosted: Wed Feb 04, 2009 3:06 pm 
Post subject: Re: Concatenate Year Month and Year Week

s7jyothi,
You've posted this in the Designer forum so that means you want to use a SQL solution. Of course we could debate the merits of doing this in Designer, or, doing this during the load of the data (in your ETL), or even doing this within the reporting tool (like WebI), however, we'll leave that debate for another day.
Before you can get help on your basic question, please tell us what RDBMS platform you are using being that if you're Oracle, some really sharp Oracle folks will pipe up with the neat things that Oracle lets you do with dates, however, if your SQL Server, someone else will give you the technique (not so elegant) to apply there.

Thanks,
John
Back to top
s7jyothi
Principal Member
Principal Member



Joined: 07 Sep 2008

Posts: 167



PostPosted: Wed Feb 04, 2009 3:33 pm 
Post subject: Re: Concatenate Year Month and Year Week

Hi BO_Chief,
The database which universe is connected is DB2, and the concatenation of year, month and year and week objects to make to six digit figure is this is possible to do with DB2 database??
Requesting for help,
Thanks.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Wed Feb 04, 2009 4:37 pm 
Post subject: Re: Concatenate Year Month and Year Week

Hi, there is no need to duplicate the post, therefore I've removed one of the two copies. Thanks.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-08-22 Yes, Virginia, You Can Refresh One Data Provider At A Time
• 2014-07-08 Airlines Could Save Millions in Fuel Costs By Providing Everyone An iPad
• 2014-06-24 Did Florida State Win National Championship By Using Big Data?
Back to top
BO_Chief
Forum Fanatic
Forum Fanatic



Joined: 06 Jun 2004
ASUG Icon
Posts: 5532
Location: Somewhere on God's Land.


flag
PostPosted: Wed Feb 04, 2009 4:55 pm 
Post subject: Re: Concatenate Year Month and Year Week

Jyothi:
DB2 and ORACLE functions are somewhat similar.. but I am not a DB2 expert

You can try this:
Code:

CHAR(TABLE.YEAR,'YYYY') || CHAR(TABLE.MONTH,'MM')


-create the object in the universe, Parse it.
-Add this object to the report and check..!

_________________
help us help you! make sure your post has the following elements:
Does it include BO version, Database, an error, a problem, a SQL for object or Condition ?
Does it include some sample data what you have?
Does it include any code you already tried ? (working or not..)
Did you explain what you want for results?
If any of those elements are missing,chances are you didn't post enough information for us to help you!
Back to top
s7jyothi
Principal Member
Principal Member



Joined: 07 Sep 2008

Posts: 167



PostPosted: Wed Feb 04, 2009 9:34 pm 
Post subject: Re: Concatenate Year Month and Year Week

Hi BO_Chief,
Thanks the function which you have suggested is working and parsing is ok, but its giving an error whiile using the concatenated object in report, and is there any changes to be made at report level? i have tried its giving an error message, and do i need to do any changes in universe??
Requesting for help and suggestions.
Thanks
Back to top
s7jyothi
Principal Member
Principal Member



Joined: 07 Sep 2008

Posts: 167



PostPosted: Wed Feb 04, 2009 9:41 pm 
Post subject: Re: Concatenate Year Month and Year Week

HI,
The database we are using is DB2 and does DB2 databasee has problems in changing and creating the concatenated objects??
Requesting for suggestions and help.
Thanks
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.0670 seconds using 17 queries. (SQL 0.0135 Parse 0.0354 Other 0.0180)
CCBot/2.0 (http://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