Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Complex formula in Business Objects Universe

Posted on 2014-10-08
6
Medium Priority
?
241 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 Panchal
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

927 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