[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Complex formula in Business Objects Universe

Posted on 2014-10-08
6
Medium Priority
?
245 Views
Last Modified: 2014-10-13
I have a object (date) inthe universe that can have the following two formats.

I want to do somehting like
If table1.type = 'twitter' then
table1.date = convert the exsiting value into a readable date format
If table1.type = 'facebook' then
table1.date = convert the exsiting value into a readable date format

Basically I am getting a date sourced from either twitter or facebook and they are different formats and I want to convert them to a consistent and readable format.

Is there a way to do that within the universe?
0
Comment
Question by:pcomb
6 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40369825
assuming
1, the underlying dbms is Oracle,
2, that table1.date is actually a varchar
3, the 2 varchar date formats are 100% consistent

then something along these lines should work

case when table1.type = 'twitter'  then to_char(to_date(table1.date,'MM-DD-YYYY'),'YYYY-MM-DD')
         when table1.type = 'facebook' then  to_char(to_date(table1.date,'MM/DD/YYYY'),'YYYY-MM-DD')
         else NULL
end

this relies on conversion to date first, then applying a format. If the source formats aren't consistent and some won't convert to date then a different approach may be needed (string manipulation); but I won't propose a solution involving string manipulation if it's not needed and without seeing what the source formats are like.
0
 
LVL 21

Expert Comment

by:Amitkumar P
ID: 40369897
Agree with PortletPaul.

If dbms is oracle then decode will also help you (that is similar to one provided by PortletPaul).

decode(table1.type, 'twitter', to_char(to_date(table1.date,'MM-DD-YYYY'),'YYYY-MM-DD'), 'facebook', to_char(to_date(table1.date,'MM/DD/YYYY'),'YYYY-MM-DD'), null)

by the way, which dbms are you using ?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40370652
It is possible.  What formats do the dates have?

Are they date strings or a numeric representation?

mlmcc
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:pcomb
ID: 40371852
PortletPaul - thanks not sure how would I add this in Business Objects I will take a look thanks
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40371903
If his code is accurate you just need to add an object (dimension) to the universe
Choose the class to add it to
Right click the class
Click NEW
Give it a meaningful name
Set it as a dimension
Put the SQL into the SELECT box
Validate the SQL
Click OK

mlmcc
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40371984
Please do note that I'm expecting you to provide the relevant format strings, both of these are pure guesses

MM-DD-YYYY (twitter)
MM/DD/YYYY (facebook)

and I don't know what format you wanted as output, I used YYYY-MM-DD
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

613 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question