Pipe delimited Field with Leading Spaces - How to Purge Spaces?

[haymarketpaul]

QV12 SR3

I have data in a field that has the following format...

Horticulture|   Housing|       Human Resources|    Internet/New Media|          Legal|     Management & Executive

So it's pipe delimited but each pipe answer (except the first one) can have any number of leading spaces

I would like to remove the leading spaces of each pipe answer somehow so i would end up with this sort of thing...

Horticulture|Housing|Human Resources|Internet/New Media|Legal|Management & Executive

Any ideas much appreciated?

[sunny_talwar · Accepted solution]

Here is one option:

Table:

LOAD FieldName,

  Concat(DISTINCT NewFieldName, '|') as NewFieldName

Group By FieldName;

LOAD *,

  Trim(SubField(FieldName, '|')) as NewFieldName;

LOAD * Inline [

FieldName

Horticulture|  Housing|      Human Resources|    Internet/New Media|          Legal|    Management & Executive

];

[Frank_Hartmann]

another option might be in script:

purgechar(Data,' ') as Data

[haymarketpaul]

Thanks Frank - unfortunately that'll remove genuine spaces in between words too

eg

HumanResources

[sunny_talwar]

Did you see my response Paul?

[haymarketpaul]

yes - thanks - looking into it now

quick question:

I've got other fields in the table too that are not delimited like UserID and Email - can i just load them in alongside the Concat in your example - will the group by affect them in any way?

[sunny_talwar]

You will have to add all your existing fields to the Group By clause.

[sunny_talwar]

Or you can do this in a resident load with a unique identifier from this table and then left join it back to your main table. Then you will only need to Group By you Unique Identifier field

[haymarketpaul]

Thanks for the reply

I've actually got 7 of these pipe delimited fields in this table - all with the same issue so i'm guessing your 2nd option would be better - there is no unique identifier but i can create one with RowNo() on the initial load.

I'll let you know how i get on

[haymarketpaul]

Perfect - Thanks again Sunny - works perfectly......

JobAlerts:

LOAD

//     Site,                                     //always says 'SupplyManagement'

     [User ID]                                as [MX JA UserID],                //Link to Madgex Users

     [Last update date]                        as [MX JA Last Update Date],

     Keywords                                as [MX JA Keywords],

     Sector                                    as [MX JA Sector],                //pipe delimited

//   Function,                                 //always blank

     Role                                    as [MX JA Job Role],             //pipe delimited

     Specialism                                as [MX JA Business Function],     //pipe delimited

     Location                                as [MX JA Location],

     [Salary Band]                            as [MX JA Salary Band],         //pipe delimited

     Hours                                    as [MX JA Working Hours],         //pipe delimited

     [Contract Type]                        as [MX JA Contract Type],         //pipe delimited

     [Employer Type]                        as [MX JA Employer Type],

     [CIPS Membership]                        as [MX JA CIPS Membership],        //pipe delimited

     RowNo()                                as [Row#]

FROM

[$(vMadgex)jobalerts_supplymanagement*.txt]

(txt, utf8, embedded labels, delimiter is ',', msq);

//Remove leading spaces after pipes

Left Join (JobAlerts)

LOAD

    [Row#]

    ,Concat(DISTINCT [MX JA Business Function New], '|')     as [MX JA Business Function New]

    ,Concat(DISTINCT [MX JA Sector New], '|')                 as [MX JA Sector New]

    ,Concat(DISTINCT [MX JA Job Role New], '|')             as [MX JA Job Role New]

    ,Concat(DISTINCT [MX JA Salary Band New], '|')             as [MX JA Salary Band New]

    ,Concat(DISTINCT [MX JA Working Hours New], '|')         as [MX JA Working Hours New]

    ,Concat(DISTINCT [MX JA Contract Type New], '|')         as [MX JA Contract Type New]

    ,Concat(DISTINCT [MX JA CIPS Membership New], '|')         as [MX JA CIPS Membership New]

Group By [Row#]

;

LOAD

    [Row#]

    ,Trim(SubField([MX JA Business Function], '|'))         as [MX JA Business Function New]

    ,Trim(SubField([MX JA Sector], '|'))                     as [MX JA Sector New]

    ,Trim(SubField([MX JA Job Role], '|'))                     as [MX JA Job Role New]

    ,Trim(SubField([MX JA Salary Band], '|'))                 as [MX JA Salary Band New]

    ,Trim(SubField([MX JA Working Hours], '|'))             as [MX JA Working Hours New]

    ,Trim(SubField([MX JA Contract Type], '|'))             as [MX JA Contract Type New]

    ,Trim(SubField([MX JA CIPS Membership], '|'))             as [MX JA CIPS Membership New]

Resident JobAlerts;