Text manipulation

[#1]

I have a table called Inventory Master, within this table there is a column called 12 month usage. The problem I am having is that the 12 month usage column combines all 12 months separated by a space. I need to be able to break the data up into separate columns so I can show each month in it's own field. Any input or assistance would be greatly appreciated.

[Gysbert_Wassenaar · Accepted solution]

Perhaps you can use the subfield function:

LOAD

     ...some fields...,

     SubField([12 month usage], ' ') as [month usage],

     ...some more fields...

FROM

     ....source....

     ;

Or

LOAD

     ...some fields...,

     SubField([12 month usage], ' ',1 ) as [month usage 1],

     SubField([12 month usage], ' ',2 ) as [month usage 2],

     ....etc

     SubField([12 month usage], ' ',12 ) as [month usage 12],

     ...some more fields...

FROM

     ....source....

     ;

[#3]

Hey Robert,

As Gysbert mentioned SUBFIELD is an answer here.

If I can suggest something: - If you are planning to create charts using this dataset, consider using The Crosstable Load on top of data generated by Gysbert's script. Having all months as one dimension will make your life much easier

BR,

Kuba

[#4]

Worked like a charm. Thank you.

Robert Galloni

Information Technology Director