Splitting DFF column after first blank

Stefan Orac

Content

I am still struggling with advanced edits in OTBI so this post for a (seemingly) easy task came up:

 

Due to a rushed implementation we have cost centers to which our employees are assigned not from the general booking key setup (cross pillar) but solemly in HR core via a DFF. The DFF is setup in a way that makes its data unusable for any further analysis/export - it combines the cost center ID and its name in the same field:

eg: 00-00-000 HQ - CostCenter

This should be divided into CostCenterID column (00-00-000) and CostCenterName column (HQ - Cost Center). So delimitated by the first blank a split has to occur.

I tried the following:

SELECT SUBSTR(cost_center, 1, INSTR(cost_center, ' ')-1) AS CostCenterID,
       SUBSTR(cost_cente, INSTR(cost_center, ' ')+1) AS CostCenterName

 

but due to my still humble knowledge of the OTBI web management studio this is doomed to fail i guess. I found a similar entry on the Taleo part in this forum, but the resolution did not give me mor insight.

 

 

I guess there is a very basic tool on board that wil resolve my issue without any fickle, but I am seemingly blind.

 

Thanks for all help and input in advance.

 

BR

Stefan

 

Gianni Ceresa

Hi Stefan,

As they finally set permission on the forum it’s possible to reply...

LEFT('00-00-000 HQ - CostCenter', LOCATE(' ', '00-00-000 HQ - CostCenter'))

SUBSTRING('00-00-000 HQ - CostCenter' FROM LOCATE(' ', '00-00-000 HQ - CostCenter')+1)

 

You can use these 2 formulas to get the left and right part of the column. As you see they are similar to your SQL but using the OBIEE logical SQL functions.

You can of course also replace LEFT by SUBSTRING if you prefer, same result.

Stefan Orac

Thanks for the detailed answer, still as OTBI/OBIEE newbie I managed to screw this up ;-)

1) I applied this code under ADVANCED>SQL. is this the correct place to do so or should I better use the formular directly on the colum at CRITERIA?

2) The example is more or less hardcoded on the example i presented in my post, but in the column name "CostCenter" we actually have hundreds of combinations:

 

00-02-0101 HQ - IT

01-02-0211 HQ - Infrastructure

01-02-0311 HQ - Development

 

and so on....

in order to completely split the whole coukmn into two separate columns I must use wildcards, but obviously I applied them at the wron position because I do not get any results. I will look up for the matching whitepaper on OBIEE wildcards in query expresions and hope to provide an answer for the next newbie that comes along with the same question :-)

 

And I'm sure there will be some :-)

Gianni Ceresa

Sorry for not having been more explicit (at 1:40AM on a phone was already not easy )

You set these formulas in the "Criteria" tab editing the formula of 2 columns (so add your "cost_center" twice and then change the formula in both to be the cost center ID and cost center name). You generally never edit the SQL (logical SQL to be precise) in the "Advanced" tab, the tool generate it for yourself based on what you do in "Criteria".

The idea is that you replace the hardcoded reference '00-00-000 HQ - CostCenter' by the column containing this information. As the rule is always the same (ID being everything from the beginning till the first space, name being eveything after the first space) it will split the value into ID and name all the time without any hardcode. I used the value itself in my example just to make sure the formulas were giving the exact value you expected.

In your case you are supposed to get something like:

LEFT("your presentation table"."your column", LOCATE(' ', "your presentation table"."your column"))

SUBSTRING("your presentation table"."your column" FROM LOCATE(' ', "your presentation table"."your column")+1)

 

That's why if you start by adding that column with the concatenated value twice you have the exact reference to it (which is always "name of presentation table"."name of the column").

Stefan Orac

I really hope my answer to you was not insulting in any way: I am EXTREMLY glad for your input - my newbie-knowledge just let to no results, but this is 100% on me.

Thank you very much for the further update.

 

I try my best and give feedback in here in this forum.

Gianni Ceresa

Not at all! Was more me complaining with myself because of doing things in the middle of the night a bit randomly instead of doing it in a more "qualitative" way in the morning

Keep going with your OTBI (OBIEE), it's fun ... at least I believe it is.

Always happy to support the community here in the forums like many others, so definitely keep coming back for any question!

[Deleted User]

Looks like you two were happy in here while I was waiting for my permissions to work again as well!

Gianni Ceresa

Don't be jealous