Splitting Values in Webi

[Former Member]

Hi,

I want to split Column A's values to Column B's values, Column C's values, Column D's values and Column E's values by using separator "/" sign.

I could get ColumnB like below formula.

ColumnB=Substr([X Query ].[ColumnA];1;Pos([X Query ].[ColumnA];"/")-1)

Can you help me to get other values of other columns, please?

I should see the table like below.

ColumnA	ColumnB	ColumnC	ColumnD	ColumnE
AB/9.40/01200/KLMN	AB	9.40	01200	KLMN
AB/9.40/01200/RSMN	AB	9.40	01200	RSMN
AB/11.90/01200/RSMN	AB	11.90	01200	RSMN
BC/25.50/0400/TRLM	BC	25.50	0400	TRML
BC/25.50/0600/PRMS	BC	25.50	0600	PRMS

Best Regards,

[tanveer1 · Accepted solution]

Hi ,

Use length([ColumnB] to find the Columns C's Starting point.

here length([Column B]) will give you 2.

So take out string from ending point of Column B and treat it same way as you extracted Column B to get the Column C and so on...

For Column D use legths of Column B and Column C

[Former Member · Accepted solution]

Hi Tanveer,

How can I extract Column B from Column A?

I can exract by using below formula but I got it by have been adding"10" integer to v_Column's length. But I am wondering why I have used 10 in the place of 1?

When I was using 1,2,3,4,5,6,7,8 or 9, I couldn't exract Column B from Cloumn A.

I have extracted Column B from Column A by using below formula but I solved this problem by adding 10 integer to length of ColumnB. It was trial and error method.

=Substr([X Query ].[ColumnA];Length([v_ColumnB]+10);Length([X Query ].[ColumnA]))

Is there any feedback about this method or any other extracting method suggestion, please?

Best Regards,

[tanveer1 · Accepted solution]

Hi,

Create below formulae to get your Column B, C and D

Column B = = Substr([Column A];1;Pos([Column A];"/")-1)

Column B INPUT = Substr([Column A];Length([Column B])+2;Length([Column A]))

Column C = Substr([Column B INPUT];1;Pos([Column B INPUT] ;"/")-1)

Column D INPUT = Substr([Column A];Length([Column B])+ Length([Column C])+3;Length([Column A]))

Column E = = Substr([Column A];Length([Column B])+ Length([Column C])+ Length([Column D])+4;Length([Column A]))

Use Column B, Column C, Column D in your table along with the Column A to get the result as shown below.

I have included all the columns just to how the workflow

Thanks,

Tanveer

[Former Member · Accepted solution]

Thank you very much Tanveer.

It works.

Best Regards,

Cihangir