filed value appears with random characterless

hatra
edited February 11, 2022 in Analytics #1

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

Comments

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

    Warning No formatter is installed for the format ipb
  • hatra
    edited September 24, 2019 #3

    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

  • 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
    
    Warning No formatter is installed for the format ipb
  • 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
    edited September 25, 2019 #6

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

    Warning No formatter is installed for the format ipb
  • 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
    edited September 25, 2019 #8

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

    Warning No formatter is installed for the format ipb
  • 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

  • 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
    
    Warning No formatter is installed for the format ipb
  • 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

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

    Warning No formatter is installed for the format ipb
  • perfect thanks, works fine.
    regards

  • 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

  • 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);

    Warning No formatter is installed for the format ipb
  • Thanks very kind :)