topic Re: Split one field into two columns with unorganized strings and conditional character in App Development

Split one field into two columns with unorganized strings and conditional character

leey8211 — Fri, 05 Jan 2024 05:08:22 GMT

Hello everyone, I have a column with unorganized strings like this, for example:

apple; NCST-00345; QTT; 83457; w456-IOKY-34569865

banana; NEL-00985; QWZ; 345876; w-9845-mbjkh-984039842

avocado; 345; SGY-983457; 2344; wPOierty

........

The goal is to split this column into two columns. One column contains all the strings before "w", the other column would be include everything with "w" and after "w", like this:

original	Column one	column two
apple; NCST-00345; QTT; 83457; w456-IOKY-34569865	apple; NCST-00345; QTT; 83457;	w456-IOKY-34569865
banana; NEL-00985; QWZ; 345876; w-9845-mbjkh-984039842	banana; NEL-00985; QWZ; 345876;	w-9845-mbjkh-984039842
avocado; 345; SGY-983457; 2344; wPOierty	avocado; 345; SGY-983457; 2344;	wPOierty
................	............	.........

So, how can I write set expressions or script code to achieve this goal? Or, what functions should I use? I tried SubField function but it's not working well. Any advice or hint would be greatly appreciated, many thanks!

Re: Split one field into two columns with unorganized strings and conditional character

PrashantSangle — Fri, 05 Jan 2024 06:47:39 GMT

Hi,

use mid() & index() to achieve the requirement

try below

Load Test, Mid(Test,1,Index(Test,'w')-1) as column1, Mid(Test,Index(Test,'w')) as column2 Inline [
Test
"apple; NCST-00345; QTT; 83457; w456-IOKY-34569865"
];

Regards,

Prashant Sangle

Re: Split one field into two columns with unorganized strings and conditional character

vinieme12 — Fri, 05 Jan 2024 07:02:58 GMT

if its always the last ';' delimited string you can also use subfield

load original
,subfield(original,subfield(original,';',-1),1) as firstCol
,subfield(original,';',-1) as secondCol
inline
[
original
apple; NCST-00345; QTT; 83457; w456-IOKY-34569865
banana; NEL-00985; QWZ; 345876; w-9845-mbjkh-984039842
avocado; 345; SGY-983457; 2344; wPOierty
];

Re: Split one field into two columns with unorganized strings and conditional character

leey8211 — Fri, 05 Jan 2024 19:35:56 GMT

Thank you Prashant, your solution works!

Re: Split one field into two columns with unorganized strings and conditional character

leey8211 — Fri, 05 Jan 2024 19:36:32 GMT

Thank you Vineeth, it helps!