How to use trim function?

[#1]

I got some useful help yesterday from this fantastic community, but now I have some additional question. I have used the following code to take away the numbers in the customer name. Unfortunately I have more customers than RAD AB ant their names are in two different excel documets (example further down). How do I do this wonderful trim function for a whole column loaded from excel (Customer)?

LOAD
       

Name,

Trim(PurgeChar(Name, '1234567890-')) as NewName

Inline [
       Name
       RAD AB

       RAD AB  -1234

];

Excel1     Place     Customer     Sale                         Excel2     Customer     Discount

           1          Cust1       123                                     Cust2 -456     23

           2          Cust2       321                                     Cust1 -987     65

[MK_QSL]

IF you use the same functions provided above, you should get your desired result.

let us know what is the problem you are facing in detail.

[MK_QSL]

Load something like below in your load for second table...

LOAD Trim(PurgeChar(Customer, '1234567890-')) as Customer,

     Discount,

     Nbr

From.....

Remove all other from below this load...

[MK_QSL · Accepted solution]

Use below script... Set your File path accordingly...

LOAD Place,

     Customer,

     Sale

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD IF(Len(Trim(PurgeChar(Customer, '1234567890-'))) >4, Trim(PurgeChar(Customer, '1234567890-')))  as Customer,

     Discount,

     Nbr

FROM

Test2.xlsx

(ooxml, embedded labels, table is Sheet1);

[sujeetsingh]

lina can you please explain the details that what you want and what is happening wrong in this script.

[MK_QSL]

Or use below script if previous will not work...

Same... set your file path accordingly..

LOAD Place,

     Customer,

     Sale

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD SubField(Customer,' ',1)  as Customer,

     Discount,

     Nbr

FROM

Test2.xlsx

(ooxml, embedded labels, table is Sheet1);

[MK_QSL]

I think you should check the output.

You have marked my answer as correct but I think it should not work.

Try the second answer, it will definitely work.