Solved

Complex formula in Business Objects Universe

Posted on 2014-10-08
6
230 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 48

Accepted Solution

by:
PortletPaul earned 500 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 100

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 100

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 48

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 62
upgrading Oracle 10g/ 11g / 11g R2 to Oracle 12c 25 72
error in my cursor 5 41
Fill Date time Field 12 25
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
How to increase the row limit in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

828 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