A function in Webi which gives the position of specific caharacter's occurrence multiple times

[HiShilpiRanjan]

Hi All,

Can you please help me with a function in Webi which gives us the position of some character's occurrence multiple times in the same string.
Pos() function gives the position of a character, however if the same character is present multiple times, how to get its position.

For example:
String(1): AP 0,G3 16.36,HK 0,B5 5.62,WY 16.53
String(1) is a column which has this concatenated text and numbers as above.

Now, I need to know the position of all commas and spaces present in this string more than once.

Can u please help me with the formula to get the following splitted from String(1):
String(1): AP 0,G3 16.36,HK 0,B5 5.62,WY 16.53

AP---- =Substr([String(1)];1;(Pos([String(1)];" ")-1))
0   ---  need formula here
G3 -- =Substr([BILLED_TAX_STRING];(Pos([BILLED_TAX_STRING];",")+1);(Pos([BILLED_TAX_STRING];" ")-1))
16.36 -- need formula here
HK    -- need formula here
0       -- need formula here
B5     -- need formula here
5.62   -- need formula here
WY     -- need formula here
16.53  -- need formula here

Please don't Hardcode the values, as we have different values in the report for String(1)
More examples for String(1) values present in the same report:

String(1)

AP 0,G3 16.36,HK 0,I5 5.62,WY 16.53

AU 30.91,WG 5.22,WY 8.9

L7 1.76,OP 0,SG 3.51,WG 1.6,WY 14.63

QR 7.97,QR 2.32,WG 3.35,WG 0

E7 .78,G8 .34,TS 15.55,WG 0,WY 0

F1 2.47,IA 8.04,KK 11.57,WG 0,WY 0

If anyone can help, would be really helpful.
Thank you in Advance🙏

Regards,
Shilpi

[HiShilpiRanjan · Accepted solution]

I found the solution.

First I followed
https://community.sap.com/t5/technology-q-a/need-to-split-string/qaq-p/9344510

After this,I counted the commas in the string using Replace and Length function.
and then applied If condition based on number of commas.

Thankyou so much for initial help after which I was able to conclude further 🙂

[nachtaktiv]

use the example/link I already gave you in your last question
to solve this with nested variables !

[HiShilpiRanjan]

Thank-you for this however not able to reach to solution yet, there is still some challenge. Please find the snippet below:

As the length of Tax_String changes, the variable is not giving correct result for all the Tax_String values.
If u see the third row above, I am not getting Tax_Code3_Value. Similarly we have different lengths of values for Tax_String.

Is there any way you can help.

What I was thinking is to apply IF condition on the number of commas in Tax_String for all my headers, however not able to find that how to count the number of commas in the Tax_String.
Is there any way to count the number of specific character in a string in Webi.

Thank-you in advance

[jaggu556]

Hi Shilpi,

As values are in uneven length and it would be little tricky to achieve at webi level. Also we will have to create many variables to handle this. 

In the past, we have achieved at Universe level with the derived table. 
What is your underlying database pointed to ?

Thanks,
Jagadeesh C

[HiShilpiRanjan]

Hi Jagadeesh,
My underlying Database is Oracle and the report is created on "Free Hand Sql" Webi version 4.2.
As u have handled such situation in Universe, Can u please explain in detail, so I may try to check if It is possible to create a separate report for this requirement
based on universe.
Also, Do u know the way to get the count of a specific character in a string in Webi.

Regards