You can translate the question and the replies:

how to parse out substring from a file-based data source

I have a data source that is based on a csv file. This file has a text column that can be pretty long and containg values that are structured as (AC12345) RANDOM TEXT Of VARYING LENGTH. The length of the string betwen the poarentheses are also varying, so I can't perform a substring with a known begin and end points. The data source are structured as follows. Text File based data source > base view > selection on top of view Within the selection view, I have tried the SPLIT command and get an array that I should be able to manage. SPLIT('\)', FIELDNAME) This ooutputs to an array where I see the following. (AC12345 RANDOM TEXT HERE Now I need to choose the first value in the array and remove the first character. The part I am struggling with is how to select the first array by combining different functions with the SPLIT function. Any guidance in this regard will be greatly appreciated.
split file-based substring
user
07-12-2022 17:04:42 -0500
code

2 Answers

Hi, The standard way to expand arrays in Denodo is to use a Flatten view. Just use the view that has the array as the base, and create a new Flatten view on top of that. It will allow you to turn the array entries into new fields and rows. The user manual has more information on creating a [Flatten view](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_derived_views/creating_flatten_views/creating_flatten_views). Once you have a Flatten view, you can manipulate the strings with a field expression in the Flatten view or in a new Selection view. Hope this helps!
Denodo Team
09-12-2022 15:35:10 -0500
code
I arrived at a simillar solution by concluding that I could not nest multiple functions within a single selection view. What I did was as follows. 1. Split the field into two value array as previously stated in a selection view - split('\)',columnname) 2. Created a second selection view where I grabbed the first value from step 1. - output1.[0].string 3. Created a third selection view where I applied the substring function to the output from step 2 to capure what I needed. - substring(output3, 1,15). The part I was stuck was on attempting to nest functions to do everything in a single step. I hope this is of value to others.
user
09-12-2022 17:19:46 -0500
You must sign in to add an answer. If you do not have an account, you can register here