Categories
How to split comma separated string into columns in OBIEE
Hi All,
I have a requirement in OBIEE where a column value (semicolon separated string) needs to be splitted into four different columns like below:
In one existing report, I have one column where the value looks like below:
| Column1 |
|---|
| Solar enclosure specialist 1 - TBD; Solar enclosure specialist 2 - TBD; inspector - TBD |
| enclosure ; Solar - TBD; inspector - TBD ; Shopkeeper |
Now this column value (semicolon separated) needs to be seperated into four columns like below:
| Passenger1 | Passenger2 | Passenger3 | Additional Passenger |
|---|---|---|---|
| Solar enclosure specialist 1 - TBD | Solar enclosure specialist 2 - TBD | inspector - TBD | NULL |
| enclosure | Solar - TBD | inspector - TBD | Shopkeeper |
Could anyone kindly help me how I can do this in OBIEE Analytics. Please note, I am using OBIEE 12c version.
Many thanks
Titas
Answers
-
You can do this either at ETL level (preferred ) or in OBIEE. Firstly, will you only have 4 columns? Is there a chance that there can be more? This will have an impact on the logic.
0 -
Hi Joel,
Good to see you, . How are you?
This needs to be done in OBIEE level actually. The four columns are fixed. Kindly help how this can be done.
Thanks
Titas
0 -
OBIEE has functions to locate the position of a string into another and to get a substring from a string. Just combine these 2 as much as needed to get the position of the 3 " ; " and then use substring to extract the value.
0 -
As @Gianni Ceresa mentioned, you just need to make use a combination of the LOCATEN & SUBSTRING functions to achieve this.
0 -
