topic Re: How to use trim function? in QlikView

How to use trim function?

Fri, 21 Feb 2014 07:23:22 GMT

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

Re: How to use trim function?

MK_QSL — Fri, 21 Feb 2014 07:30:12 GMT

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.

Re: How to use trim function?

MK_QSL — Fri, 21 Feb 2014 07:51:45 GMT

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...

Re: How to use trim function?

MK_QSL — Fri, 21 Feb 2014 08:21:11 GMT

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);

Re: How to use trim function?

sujeetsingh — Fri, 21 Feb 2014 08:23:05 GMT

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

Re: How to use trim function?

MK_QSL — Fri, 21 Feb 2014 08:23:15 GMT

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);

Re: How to use trim function?

MK_QSL — Fri, 21 Feb 2014 09:00:40 GMT

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.