Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 '|');
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 '|');
Thanks, this worked well!