filed value appears with random characterless

Hi All,
I have a column that has random correctors and I want to exclude it with BIRT function,
value appears like "<~cb==bt=columnxc> London,Paris,Berlin<~ccl>
I want to only show London, Paris, London and preferably like below format in one cell
London
Paris
Berlin
could you please advice me what function to use and the syntax's please

thanks

Find more posts tagged with

Comments

jfranken

Create a computed column in the Data Set. Use JavaScript to find the position of the first ">" and the position of the last "<" in the data. Use slice() or one of the other methods to parse out just the text between those two positions from the data.

hatra

Thanks Jeff,
any chance of a example please, I am not of the code, syntax to write that javascript code.
I might have missed explaining all detail
as per my previous example
column return data as below
"<~cb==bt=columnxc> London,Paris,Berlin<~ccl>
but also if the value is only one ie London , than it it clear and no need to exclude any unwanted character
so I am looking for to exclude before and after if the value is more than one
for example
1. "<~cb==bt=columnxc> London,Paris,Berlin<~ccl>" // need to trim before and after and return only London,Paris,Berlin ( preferably in separate row
2. "<~cb==bt=columnxc> London,Paris<~ccl>" // need to trim before and after and return only London,Paris ( preferably in separate row
3. London // no need to trim or exclude unwanted character
thanks

jfranken

Something like this might work (not tested):

var str = row["my_data"]
if (str.startsWith("<")) {
   var start = str.indexOf(">");   // get end position of first tag
   var end = str.lastIndexOf("<");  // get start position of second tag
   str = str.slice(start, end);  // get text in between tags
}
str

hatra

Thanks Jeff this is a solution I was exactly looking for,
but i get an error "Cannot find function StartsWith in object",
I search the "startsWith" function online and try to modify and define it before calling it but no luck.
any idea why this function isn't working when executing the computed column?

thanks

jfranken

It looks like an issue with the case- startsWith. Also, check for nulls and skip them.

hatra

Thanks, by Nulls, if you mean null values there are none on my sample data,
i believe the issue is startsWith is a unknown function in opentext, could find a replacement for this function.
thanks

jfranken

Maybe str.indexOf("<") == 0

hatra

i gave it try something as below but no luck
var str = row["my_data"]
if (str.startsWith("<")) {
var start = str.indexOf(">"); // get end position of first tag
var end = str.lastIndexOf(("<")==0); // get start position of second tag
str = str.slice(start, end); // get text in between tags
}
str
thanks

jfranken

I was thinking something like this. You may need to add or subtract 1 from 'start' and 'end'.

   var str = row["my_data"];
   if(str != null && str != undefined) {
     if (str.indexOf("<") == 0) {
       var start = str.indexOf(">");   // get end position of first tag
       var end = str.lastIndexOf("<");  // get start position of second tag
       str = str.slice(start, end);  // get text in between tags
     }
  }   
  str

hatra

Hey Jeff, this is brilliant, it does all the work but only one small issue
as i mentioned my data is something like this "<~cb==bt=columnxc> London,Paris,Berlin<~ccl>"
with the latest code you sent i get "> London,Paris,Berlin" very close only one character is unwanted and still appears.

thanks

jfranken

var start = str.indexOf(">") + 1;

hatra

perfect thanks, works fine.
regards

hatra

Hey just one other question regarding above solution which works fine as I wanted so data appease as expected
**column A column B column C **
product1 2345 Paris, London, Rome
product2 2346 London, New York, Tokyo

Is it possible to have the able data to generate as below
**column A column B column C **
product1 2345 Paris,
product1 2345 London
product1 2345 Rome
product2 2346 London
product2 2346 New York
product2 2346 Tokyo
basicly where the data separated by deliminator to create a new rows, but I wouldn't know the number of columns, I am thinking to have the column dynamic?
any idea please ?
thanks

jfranken

This transitioning from support to development. I'll give this one more go, but please look up the code for things like string parsing.

Here is code to convert:

"<~cb==bt=columnxc>product1 2345 London, Paris, Rome<~ccl>

To:

product1 2345 London
product1 2345 Paris
product1 2345 Rome

var str = row["my_data"];
if(str != null && str != undefined) {
if (str.indexOf("<") == 0) {
var start = str.indexOf(">") + 1; // get end position of first tag
var end = str.lastIndexOf("<"); // get start position of second tag
str = str.slice(start, end); // get text in between tags
}
}
var pos = str.split(" ", 2).join(" ").length;
var s = str.substr(0,pos);
var s1 = str.substr(pos);
s + s1.split(",").join("\n" + s);

hatra

Thanks very kind