Break string into multiple lines

[pascaldijkshoor]

Hi everyone,

I have a table with the following data: LINE_DESCRIPTION, Company, Amount. The field 'LINE_DESCRIPTION'  sometimes contains a line like this: 1506 / 14008 / 15040 / 7610 toll week 8. I would like to retrieve a new field called ASSET_NR, which contains every number in the line_description separately.  

So if the line in the table is as follows:

| LINE_DESCRIPTION                                        | Company       | Amount | 

| 1506 / 14008 / 15040 / 7610 toll week 8 | Company XX | 54,30       | 

the new lines have to be like this: 

| LINE_DESCRIPTION                                        | Company       | Amount |  ASSET_NR |

| 1506 / 14008 / 15040 / 7610 toll week 8 | Company XX | 54,30       | 1506              |

| 1506 / 14008 / 15040 / 7610 toll week 8 | Company XX | 54,30       | 14008           |

| 1506 / 14008 / 15040 / 7610 toll week 8 | Company XX | 54,30       | 15040           |

| 1506 / 14008 / 15040 / 7610 toll week 8 | Company XX | 54,30       | 7610             |

[trdandamudi · Accepted solution]

Please look at the function called SubField() and that will help you to get what you need.

Might be something like below:

Data:

LOAD DISTINCT
LINE_DESCRIPTION,Company,Num#(Amount),
SubField(LINE_DESCRIPTION,'/') as ASSET_NR
;

Load * inline [
LINE_DESCRIPTION|Company|Amount
1506 / 14008 / 15040 / 7610 toll week 8|Company XX|54,30]
(delimiter is '|');

[pascaldijkshoor]

Thanks, this worked well!