Data transformation

[0li5a3a]

Hi Guys,

I need to  rename some information in my column  and I don't know.

This is my example

TEST1:

Load

         id,

          [voucher no],

          description

from

abc.qvd

In the @description column  I got information like :

AB20

BA20,

20-11-16 20 pounds,

24-12-2015 15 pounds,

13 pounds

I want to rename all the description with this format (20-11-16 20 pounds, 24-12-2015 15 pounds,13 pounds) rename ReABC because later I need to make a chart and I need to count all the ReABC.

Please advise me!

Best Regards,

Costy

[antoniotiman]

Hi

LOAD *,

If(description Like '*pounds*',description) as ReABC

from ABC.qvd;

Regards,

Antonio

[antoniotiman]

If You don't need description field then

TEST1:

Load

         id,

          [voucher no],

          description as ReABC

from

abc.qvd

Where description Like '*pounds*' ;

[Chanty4u]

try this

TEST1:

LOAD * INLINE [

    id, description

    1, AB20

    2, BA20

    3, 20-11-16 20 pounds

    4, 24-12-2015 15 pounds

    5, 13 pounds

] ;

Result:

LOAD *,

description as ReABC

Where description like '*pound*'

from

....

[ToniKautto]

Not sure I understood you correctly. Would this be what you are looking for?

LOAD

   id,

   [voucher no],

   If( description like '* pounds', 'ReABC', description) AS description

from

abc.qvd

[antoniotiman]

Hi Tony,

If You need Value 'ReABC' in Field  (not Field Name) You are correct.

However

Expression

1

Count(ReABC)

2

Count({<description={'ReABC'}>} description)

Regards,

Antonio

[#7]

table1:

load *

inline

[name

abc,

xyz,

12 pound,

23 pound,

34 pound] 

;

table2:

load name as PoundName Resident table1

where name like '*pound*' ;

[0li5a3a]

Hi ,

Is works just for half of my data, the problem is I got (20-04-16 14.99 voucher )this data I can't put it in my ReABC.

I tried something like : if(description like'*pound' or 'dd-mm*','REABC') AS Description  but   if I got something liken 20-04-16 14.99 is not going to ReABC.

One more think I suppose to see something like :

ReABC

AB20

BA20

Thanks

[0li5a3a]

description contain  something like below :

20-11-16 20 pound

20-12-15 14.15 voucher code

13 pounds voucher

RMab,

RMde

I want to have to the end  something like this:

ReABC,

RMab,

RMde

[ToniKautto · Accepted solution]

How about using WildMatch() to look for any of multiple patterns?

LOAD
   If( WildMatch(description, '* pounds', '* voucher'), 'ReABC', description) AS description
Inline [
description
AB20
BA20,
20-11-16 20 pounds,
24-12-2015 15 pounds,
13 pounds
20-04-16 14.99 voucher 
];