How to split the column sell with "," in OutSystems reactive app?

I have two tables, one contains name and certification, another one has certification and category. I want to combine both the tables in aggregate. Is that possible to split the cell certification with ","? if possible please give the solution.




mvp_badge
MVP

Hello there Densingh,

Hope you're doing well.


If you're using a Reactive Web Application, you can use split() JavaScript function to perform this split. This way will allow you to avoid a server call just to execute the String_Split action from Text extension.


There is already a component for reactive that implements this JavaScript function, so you may want to check it:

https://www.outsystems.com/forge/component-overview/9331/split-string


Hope that this helps you!


Kind regards,

Rui Barradas

I want to combine both the tables with the 1st table and the 2nd table certification column.

Hi Daniel,

If I understand correctly, you want to do that join between the Certification column from both tables in your aggregate. Is it right?

And your problem is that one of the columns has all the certifications in just one cell, so you need to separate then in order to join with the other one.

Well, you could do it using both entities as source of the aggregate. Service Studio will inform that you must set a condition. Click on "Condition must be set" and put something like this: 

Table1.Certification like "%" + Table2.Certification + "%"

It will verify if the Certification on Table2 is inside the Certification on Table1.

If it is what you need, I hope it helps.

Best regards


"Condition must be set" in Service Studio:

If I give the condition with like operator it is not giving the right answer. How to filter the record?

What are the wrong results that you are facing?

If I have the certification name "AWS certified solution architect" and "AWS certified solution architect - associate" in one table, and I am comparing it with the "AWS certified solution architect" name in another table". Then it will match both the certification name. Because of "like" operator.

My condition is 

Sheet4.Certification like "%" + Sheet1.CertificationsDone + "%"

I have tried some regular expressions here, but SQL wildcards are very limited. It is possible to improve the like clause like:

Sheet4.Certification like "%" + Sheet1.CertificationsDone + "%[^-]"

But the problem will always remain and other patterns may break it. I don't think it is possible to be done only using SQL.

Try this...

Sheet4.Certification like "%" + substring(Sheet1.CertificationsDone, 1, pathindex("%,%", Sheet1.CertificationsDone) -1) + "%"
AND
len(Sheet4.Certification) = len(substring(Sheet1.CertificationsDone, 1, pathindex("%,%", Sheet1.CertificationsDone) -1))

Jean Bastos, this is a good ideia. But, in this way, the result will only match if the Certification was the first one. But, using your ideia, It is possible to make some changes in order to make it work.

I couldn't test it right now, but I think that in this way it could be done:

-- if the Sheet4.Certification is the first or it is in the middle of the string
Sheet4.Certification like substring(Sheet1.CertificationsDone,
          -- first occurrence of Sheet4.Certification
          pathindex(Sheet4.Certification, Sheet1.CertificationsDone),
          -- first occurrence of "," after the first occurrence of Sheet4.Certification
          pathindex(",", substring(Sheet1.CertificationsDone, 
                         pathindex(Sheet4.Certification, Sheet1.CertificationsDone), 
                         len(Sheet1.CertificationsDone))))
OR
-- if the Sheet4.Certification is the last one
Sheet4.Certification like substring(Sheet1.CertificationsDone,
          -- first occurrence of Sheet4.Certification
          pathindex(Sheet4.Certification, Sheet1.CertificationsDone),
          -- the end of the string
          len(Sheet1.CertificationsDone)))

Please, help me to tweak this query, because I didn't test it, but I think that in this way it is possible to work.

Hope it helps.

Regards.

I, maybe, not see a big scope on this... but by example above CertificationsDone always have the string bigger than Certification.

Than I think my script could work.

If I'm wrong... it have to be your script.

It have to be tested in both cases :).

Regards

You are right. CertificationsDone has the biggest string, but, if the Certification to be searched is the second one on that string, this

pathindex(",", Sheet1.CertificationsDone)

will result in the index of the first ",", which is before the second certification on the list. Am I correct?


So, this

Sheet4.Certification like "%" + substring(Sheet1.CertificationsDone, 1, pathindex("%,%", Sheet1.CertificationsDone) -1) + "%"

will return false every time that the certification was in a position after first.

With this in mind, I just changed your script to make it flexible. What you think?

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.