Find and Replace function

[realpixel]

Hello,

I want to use a replace function to find and replace a value by another.

For example

[CUSTOMER]         

customer1

customer2

customer3

customer4

customer5

customer6

customer7

customer8

customer9

customer10

customer11

customer12

.

.

.

customer1,2 and 3 must be replaced by customer1

customer 4,5,6 must replaced by customer4

and

customer 7,8,9 must replaced by customer7

[realpixel]

Ok I try to explain.

See under the result a part of result

[LIEU POS]	[TRANS]	[DATE MOV]	[DATE ENTREE]	[TOT CON]
TOT CON	GETMA	08/01/2013	10/12/2012
TOT CON	J-B-Z	28/02/2013	19/02/2013
TOT CON	J-B-Z	04/03/2013	19/02/2013
TOT CON	SV-CON	16/03/2013	04/03/2013	TOT CON
TOT ET P	GETMA	05/03/2013	28/11/2012
TOT ET P	J-B-Z	28/02/2013	19/02/2013
TOT ET P	SV-CON	07/01/2013	15/12/2012	TOT CON
TOT ET P	SV-CON	08/01/2013	15/12/2012	TOT CON
TOT ET P CON	SV-CON	02/01/2013	10/12/2012	TOT CON
TOT ET P CON	SV-CON	04/01/2013	10/12/2012	TOT CON
TOUTY	SUPER	25/02/2013	16/02/2013
TOUTY	SUPER	08/03/2013	26/02/2013
TOUTY	SUPER	11/03/2013	25/02/2013

How to transfert or copy all data not concern by the scipt under from [LIEU POS] to [TOT CON]? For example [TOUTY] is not transfert, copy to [TOT CON], the field is blank.

Maybe it possible to add a function 'else' in a part of the script under?

LOAD [LIEU POS],

if(TRANS=peek(TRANS),Peek([TOT CON]),[LIEU POS]) as [TOT CON], // else other date not concern?

TRANS

Resident a order by [LIEU POS];         

DROP Table a; 

[#3]

It should work (Perumal last example). It completes all TOT_CON column at my pc.

if(TRANS=peek(TRANS)    ,      Peek([TOT CON])       ,       [LIEU POS]) as [TOT CON]

if  ( condition             ,          true                 ,        else)

[realpixel]

Maybe my previous message was not clear.

I want to obtain the result under.

[LIEU POS]	[TRANS]	[DATE MOV]	[DATE ENTREE]	[TOT CON]
TOT CON	GETMA	08/01/2013	10/12/2012	TOT CON
TOT CON	J-B-Z	28/02/2013	19/02/2013	TOT CON
TOT CON	J-B-Z	04/03/2013	19/02/2013	TOT CON
TOT CON	SV-CON	16/03/2013	04/03/2013	TOT CON
TOT ET P	GETMA	05/03/2013	28/11/2012	TOT ET P
TOT ET P	J-B-Z	28/02/2013	19/02/2013	TOT ET P
TOT ET P	SV-CON	07/01/2013	15/12/2012	TOT CON
TOT ET P	SV-CON	08/01/2013	15/12/2012	TOT CON
TOT ET P CON	SV-CON	02/01/2013	10/12/2012	TOT CON
TOT ET P CON	SV-CON	04/01/2013	10/12/2012	TOT CON
TOUTY	SUPER	25/02/2013	16/02/2013	TOUTY
TOUTY	SUPER	08/03/2013	26/02/2013	TOUTY
TOUTY	SUPER	11/03/2013	25/02/2013	TOUTY

[realpixel]

Sorry I did not see your message.

LOAD [LIEU POS],

if(TRANS=peek(TRANS),Peek([TOT CON]),[LIEU POS]) as [TOT CON],

if(condition,true,else),

TRANS

Resident a order by [LIEU POS];         

It's not work, 'condition not find.

[realpixel]

It's work fine when the condition is true.

LOAD [LIEU POS],

if(TRANS=peek(TRANS),Peek([TOT CON]),[LIEU POS]) as [TOT CON],

TRANS

Resident a order by [LIEU POS];         

The problem is when the condition is false. The column [TOT CON] is empty.

[LIEU POS]      [TRANS]                                   [DATE MOV]     [DATE ENTREE]     [TOT CON]

TOUTY	SUPER	25/02/2013	16/02/2013
TOUTY	SUPER	08/03/2013	26/02/2013
TOUTY	SUPER	11/03/2013	25/02/2013

[CELAMBARASAN]

Hi,

Can you tell me this is the way you need?

If(IsNull([TOT CON]) OR len([TOT CON]), [LIEU POS], [TOT CON]) AS [TOT CON]

I think you need to get the information of [LIEU POS] column under [TOT CON] if [TOT CON] is empty.

am I right?

[realpixel]

Hi,

Thank you for your reply, yes I need to have the information of [LIEU POS] column under [TOT CON] if condition [TRANS] is false.

a:

LOAD * INLINE [

    'LIEU POS','TRANS'

    'TOT CON','SV-CON'

    'TOT ET P','SV-CONGO'

    'TOT ET P CON','SV-CON'];

LOAD [LIEU POS],

if(TRANS=peek(TRANS),peek([TOT CON]),[LIEU POS]) as [TOT CON],

If(IsNull([TOT CON]) OR len([TOT CON]), [LIEU POS], [TOT CON]) AS [TOT CON],

TRANS

Resident a order by [LIEU POS];         

When I add

If(IsNull([TOT CON]) OR len([TOT CON]), [LIEU POS], [TOT CON]) AS [TOT CON],

Field <TOT CON> not find.

[CELAMBARASAN]

I misunderstood ur requirements.

Check with this

a:

LOAD * INLINE [

    'LIEU POS','TRANS'

    'TOT CON','SV-CON'

    'TOT ET P','SV-CONGO'

    'TOT ET P CON','SV-CON'];

TransMap:

Mapping Load

     TRANS,

     [LIEU POS]

Resident a;

TableName:

LOAD [LIEU POS],

TRANS,

Applymap('TransMap', TRANS) AS [TOTCON]

Resident a order by [LIEU POS];        

[realpixel]

HI,

I need to do this

if  [CUSTOMER] = 'TOT ET P CON' or 'TOT ET P' and the condition [TRANS] = 'SV-CON' is true, column [TOT CON] = [TOT CON]

Now if condition [TRANS] is not egal (false) at 'SV-CON', for example equal at another value like GET, DL, SOCO or other, the value must be equal at the column [CUSTOMER] same value copied.

If I use this script.

a:

LOAD * INLINE [

    'LIEU POS','TRANS'

    'TOT CON','SV-CON'

    'TOT ET P','SV-CONGO'

    'TOT ET P CON','SV-CON'];

TransMap:

Mapping Load

     TRANS,

     [LIEU POS]

Resident a;

TableName:

LOAD [LIEU POS],

TRANS,

Applymap('TransMap', TRANS) AS [TOTCON]

Resident a order by [LIEU POS];        

The value 'TOT CON' is correctly remplaced but when the column [TRANS] is not equal at 'SV-CON' the column [TOT CON] is empty.

CUSTOMER	TRANS	TOT CON
TOT ET P CON	SV-CON	TOT CON
TOT ET P CON	SV-CON	TOT CON
TOT ET P CON	SV-CON	TOT CON
TOT CON	GET
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOT ET P	SV-CON	TOT CON
TOTAL E ET P	DL
TOTAL E ET P	DL
TOTAL E ET P	DL
UBIPHARM	SOCO
UBIPHARM	SOCO
TSANKO	SITTRA
TSANKO	SITTRA

If

[CELAMBARASAN]

Do you need TRANS in that place?

means

Applymap('TransMap', TRANS, TRANS) AS [TOTCON]

Regards,

Celambarasan