topic Re: Using SubField with more than one 'field_no' in App Development

Using SubField with more than one 'field_no'

marcomatta — Mon, 30 Oct 2023 11:03:13 GMT

Hi everyone,

I have some raw data which need to be cleaned. One of the cleaning steps consists in separate rows based on a specific delimiter and the script function SubField() helps in that. However, SubField() requires as a parameter which of the substrings to have as output. I need to have more than one substring as output for a given input string.
Here's an example:

INPUT_FIELD	OUTPUT_FIELD_DESIRED
Description order - ID-1234 - Some other text - ID-4567	1234
Description order - ID-1234 - Some other text - ID-4567	4567

SubField(INPUT_FIELD, 'ID-', 2) and SubField(INPUT_FIELD, 'ID-', 3) would give the desired output but of course I cannot assign them to the same field in the Section

Is there anything I am missing with the use of SubField()? Could you please help me solve this? Maybe there is a workaround that I'm not seeing.

Thanks a lot for your help!

Re: Using SubField with more than one 'field_no'

vincent_ardiet_ — Mon, 30 Oct 2023 11:08:16 GMT

Do you have always 2 "ID -" in your input string?
Do you have an identifier/key field for each string?

Re: Using SubField with more than one 'field_no'

marcomatta — Mon, 30 Oct 2023 11:12:17 GMT

The 2 "ID-" in one input string is a special case which I'm struggling with; in the other cases I can obtain the desired output.

Yes, I do always have an identifier/key field for each string

Re: Using SubField with more than one 'field_no'

vincent_ardiet_ — Mon, 30 Oct 2023 11:16:08 GMT

So you could try to extract the Key, First ID, Second ID.
And then in a second time, extract Key + First ID as Output and concatenate Key + Second ID as Output.

Re: Using SubField with more than one 'field_no'

Sivapriya_d — Tue, 31 Oct 2023 03:09:20 GMT

Hi ,

You can try this.

Load *,
Subfield(Replace((KeepChar(Input,'0123456789-')),'--',''),'-') as Out;
;
load * Inline [
Input
'Description order - ID-1234 - Some other text - ID-4567',
'Description order - ID-1234 - Some other text - ID-4567'
];

Output:

Thanks,