Using SubField with more than one 'field_no'

[marcomatta]

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!  

[vincent_ardiet_]

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

[marcomatta]

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

[vincent_ardiet_]

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.

[Sivapriya_d]

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,