topic Incorta formula in Dashboards & Analytics Discussions

Incorta formula

Incortaconsulta — Wed, 08 Jun 2022 11:00:29 GMT

Hi Team - Wondering whether below oracle Sql can be achieved using Incorta formula?

segment1 is the input

formula_required is the output.

select segment1, substr(segment1,1,(length(segment1)-instr(reverse(segment1),'-'))) AS formula_required from dual;

Sample input and output

Segment1	formula_required
9211-5082	9211
86062C-C16-SPS	86062C-C16
U2653A-ACF	U2653A
U2652A-OC-INCDOC	U2652A-OC

Note : Basically it has to print the characters before the last '-'

Re: Incorta formula

michael_ross — Wed, 08 Jun 2022 13:22:56 GMT

Hi @Incortaconsulta

I was able to replicate this functionality using the substring and findLast functions in the formula builder. This formula performs a substring starting at position 0, and then uses the findLast function to find the position of the last dash in the string, and ends the string there.

The example below shows the formula in an insight, but the same formula can be used in the Schema or Business Schema areas if you want to persist the results for others to use without having to type the formula every time in an insight.

I included your examples in the sample data, and I added an extra example with three dashes so you could see that it still works with more than two dashes.

substring( Community_Questions.StringQuestion06082022.Sample_Data, 0, (findLast( "-", Community_Questions.StringQuestion06082022.Sample_Data )) )

Re: Incorta formula

Incortaconsulta — Fri, 10 Jun 2022 05:19:38 GMT

Hi Mike - It worked like a gem, Kudos and thanks !!