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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

PortletPaulEE Topic AdvisorCommented:
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.

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 ?
mlmccCommented:
It is possible.  What formats do the dates have?

Are they date strings or a numeric representation?

mlmcc
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pcombAuthor Commented:
PortletPaul - thanks not sure how would I add this in Business Objects I will take a look thanks
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
PortletPaulEE Topic AdvisorCommented:
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
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.