Complex formula in Business Objects Universe

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?
pcombAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amitkumar PSr. ConsultantCommented:
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
mlmccCommented:
It is possible.  What formats do the dates have?

Are they date strings or a numeric representation?

mlmcc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcombAuthor Commented:
PortletPaul - thanks not sure how would I add this in Business Objects I will take a look thanks
0
mlmccCommented:
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
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.