Find and Replace function

realpixel · ‎2013-03-20

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 · ‎2013-03-25

I need TRANS for the condition but if there is an another solution, I'm interested.

Sorry I'm little lost, where I must to put

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

I have now this script.

a:

LOAD * INLINE [

'LIEU POS','TRANS'

'TOT CON','SV-CON'

'TOT ET P','SV-CON'

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

TransMap:

Mapping Load

TRANS,

[LIEU POS]

Resident a;

TableName:

LOAD [LIEU POS],

TRANS,

Applymap('TransMap', TRANS,TRANS) AS [TOT CON]

Resident a order by [LIEU POS];

I have always the same problem when coondition is true 'TOT CON' appears in column [TO CON] but if the condition is false no result in the column [TOT CON].

CELAMBARASAN · ‎2013-03-25

a:

LOAD * INLINE [

'LIEU POS','TRANS'

'TOT CON','SV-CON'

'TOT ET P','SV-CON'

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

'TOT ESD', 'GAME'];

TransMap:

Mapping LOAD TRANS,[LIEU POS] INLINE [

LIEU POS,TRANS

TOT CON,SV-CON

TOT ET P,SV-CON

TOT ET P CON,SV-CON];

TableName:

LOAD [LIEU POS] AS [LIEU POS1],

TRANS AS TRANS1,

Applymap('TransMap', TRANS,TRANS) AS [TOT CON]

Resident a order by [LIEU POS];

Is this your need?

realpixel · ‎2013-03-26

Hi,

Is no exactly that I want. See under the result

Why 'TOT ESD' appears in column [LIEU POS]? And why 'GAME' appears in column [LIEU POS]?

It seem that [TOT CON] can have only two value 'GAME' or [TOT CON]

[LIEU POS]	[TRANS]	[DATE MOV]	[DATE ENTREE]	[TOT CON]
TLC	HANDL	22/02/2013	15/01/2013	GAME
TLC	HANDL	22/02/2013	15/01/2013	TOT CON
TMC	TRANS MARI	20/02/2013	13/01/2013	GAME
TMC	TRANS MARI	20/02/2013	13/01/2013	TOT CON
TO	MAIN TRANS	03/01/2013	07/12/2012	GAME
TO	MAIN TRANS	03/01/2013	07/12/2012	TOT CON
TOP	INTER TRANS	18/02/2013	23/01/2013	GAME
TOP	INTER TRANS	18/02/2013	23/01/2013	TOT CON
TOT ESD	GAME			GAME
TOT ESD	GAME			TOT CON
TOT	HANDL	19/01/2013	20/12/2012	GAME
TOT	HANDL	19/01/2013	20/12/2012	TOT CON
TOT	GET	05/01/2013	30/10/2012	GAME
TOT	GET	05/01/2013	30/10/2012	TOT CON
TOT	J-B-Z	16/01/2013	19/12/2012	GAME
TOT	J-B-Z	16/01/2013	19/12/2012	TOT CON
TOT	PANA	12/01/2013	30/11/2012	GAME
TOT	PANA	12/01/2013	30/11/2012	TOT CON
TOT	SV-CON	02/01/2013	22/11/2012	GAME
TOT	SV-CON	02/01/2013	22/11/2012	TOT CON
TOT CON	DLH	28/02/2013	19/02/2013	GAME
TOT CON	DLH	28/02/2013	19/02/2013	TOT CON
TOT CON	GET	08/01/2013	10/12/2012	GAME
TOT CON	GET	08/01/2013	10/12/2012	TOT CON
TOT CON	SV-CON	02/01/2013	28/11/2012	GAME
TOT CON	SV-CON	02/01/2013	28/11/2012	TOT CON
TOT ET P	DLH	28/02/2013	19/02/2013	GAME
TOT ET P	DLH	28/02/2013	19/02/2013	TOT CON
TOT ET P	GET	05/03/2013	28/11/2012	GAME
TOT ET P	GET	05/03/2013	28/11/2012	TOT CON
TOT ET P	GET	07/03/2013	28/11/2012	GAME
TOT ET P	GET	07/03/2013	28/11/2012	TOT CON
TOT ET P	SV-CON	07/01/2013	15/12/2012	GAME
TOT ET P	SV-CON	07/01/2013	15/12/2012	TOT CON
TOT ET P CON	SV-CON	05/01/2013	10/12/2012	GAME
TOT ET P CON	SV-CON	05/01/2013	10/12/2012	TOT CON

I need to do this.

If [LIEU POS] = 'TOT ET P CON' or 'TOT ET P' and [TRANS]='SV-CON', the value in column [TOT CON] must be [TOT CON]

Now if [TRANS] is not egal at 'SV-CON' no modification, column [LIEU POS] = columne [TOT CON]

See under the final result

[LIEU POS]	[TRANS]	[DATE MOV]	[DATE ENTREE]	[TOT CON]
TLC	HANDL	22/02/2013	15/01/2013	TLC
TLC	HANDL	22/02/2013	15/01/2013	TLC
TMC	TRANS MARI	20/02/2013	13/01/2013	TMC
TMC	TRANS MARI	20/02/2013	13/01/2013	TMC
TO	MAIN TRANS	03/01/2013	07/12/2012	TO
TO	MAIN TRANS	03/01/2013	07/12/2012	TO
TOP	INTER TRANS	18/02/2013	23/01/2013	TOP
TOP	INTER TRANS	18/02/2013	23/01/2013	TOP
TOT ESD	GAME			GAME	Must be erased
TOT ESD	GAME			TOT CON	Must be erased
TOT	HANDL	19/01/2013	20/12/2012	TOT
TOT	HANDL	19/01/2013	20/12/2012	TOT
TOT	GET	05/01/2013	30/10/2012	TOT
TOT	GET	05/01/2013	30/10/2012	TOT
TOT	J-B-Z	16/01/2013	19/12/2012	TOT
TOT	J-B-Z	16/01/2013	19/12/2012	TOT
TOT	PANA	12/01/2013	30/11/2012	TOT
TOT	PANA	12/01/2013	30/11/2012	TOT
TOT	SV-CON	02/01/2013	22/11/2012	TOT
TOT	SV-CON	02/01/2013	22/11/2012	TOT
TOT CON	DLH	28/02/2013	19/02/2013	TOT CON
TOT CON	DLH	28/02/2013	19/02/2013	TOT CON
TOT CON	GET	08/01/2013	10/12/2012	TOT CON
TOT CON	GET	08/01/2013	10/12/2012	TOT CON
TOT CON	SV-CON	02/01/2013	28/11/2012	TOT CON
TOT CON	SV-CON	02/01/2013	28/11/2012	TOT CON
TOT ET P	DLH	28/02/2013	19/02/2013	TOT ET P
TOT ET P	DLH	28/02/2013	19/02/2013	TOT ET P
TOT ET P	GET	05/03/2013	28/11/2012	TOT ET P
TOT ET P	GET	05/03/2013	28/11/2012	TOT ET P
TOT ET P	GET	07/03/2013	28/11/2012	TOT ET P
TOT ET P	GET	07/03/2013	28/11/2012	TOT ET P
TOT ET P	SV-CON	07/01/2013	15/12/2012	TOT CON
TOT ET P	SV-CON	07/01/2013	15/12/2012	TOT CON
TOT ET P CON	SV-CON	05/01/2013	10/12/2012	TOT CON
TOT ET P CON	SV-CON	05/01/2013	10/12/2012	TOT CON

realpixel · ‎2013-03-27

Hello,

Someone have an idea how to solve this problem.

I try this scritp

a:

LOAD * INLINE [

'LIEU POS','TRANS'

'TOT CON','SV-CONGO'

'TOT','SV-CONGO'

'TOT ET P','SV-CONGO'

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

LOAD [LIEU POS],

if([TRANS]='SV-CON' and [LIEU POS]='TOT ET P','TOT ',[LIEU POS]) as [NEW],

TRANS

Resident a order by [LIEU POS];

The result is something like that

LIEU POS	TRANS	DATE MOV	DATE ENTREE	NEW
A N C	DISTRIBUTION	10/01/2013	15/12/2012
A N C	INTER TRANS SER	07/01/2013	07/12/2012
TOT	LINKS HANDL	19/01/2013	20/12/2012
TOT	GET	05/01/2013	30/10/2012
TOT	J-B-Z	16/01/2013	19/12/2012
TOT	PANAL	12/01/2013	30/11/2012
TOT	SV-CON	02/01/2013	22/11/2012	TOT
TOT	SV-CON	02/01/2013	28/11/2012	TOT
TOT CON	DLH	28/02/2013	19/02/2013
TOT CON	GET	08/01/2013	10/12/2012
TOT CON	SV-CON	04/02/2013	11/01/2013	TOT
TOT ET P	DLH	28/02/2013	19/02/2013	TOT
TOT ET P	DLH	01/03/2013	19/02/2013
TOT ET P	DLH	04/03/2013	19/02/2013
TOT ET P	DLH	05/03/2013	19/02/2013
TOT ET P	GET	05/03/2013	28/11/2012
TOT ET P	GET	07/03/2013	28/11/2012
TOT ET P	SV-CON	08/01/2013	15/12/2012	TOT
TOT ET P CON	SV-CON	02/01/2013	10/12/2012	TOT
TPI	SV-CON	03/01/2013	03/12/2012
TPI	TRANS INTER	16/03/2013	10/03/2013

But when when confition is not equal to 'SV-CON' column new is empty

I when to arrive at this result

LIEU POS	TRANS	DATE MOV	DATE ENTREE	NEW
A N C	DISTRIBUTION	10/01/2013	15/12/2012	A N C
A N C	INTER TRANS SER	07/01/2013	07/12/2012	A N C
TOT	LINKS HANDL	19/01/2013	20/12/2012	TOT
TOT	GET	05/01/2013	30/10/2012	TOT
TOT	J-B-Z	16/01/2013	19/12/2012	TOT
TOT	PANAL	12/01/2013	30/11/2012	TOT
TOT	SV-CON	02/01/2013	28/11/2012	TOT
TOT CON	DLH	28/02/2013	19/02/2013	TOT CON
TOT CON	GET	08/01/2013	10/12/2012	TOT CON
TOT CON	SV-CON	04/02/2013	11/01/2013	TOT
TOT ET P	DLH	28/02/2013	19/02/2013	TOT
TOT ET P	DLH	01/03/2013	19/02/2013	TOT ET P
TOT ET P	DLH	04/03/2013	19/02/2013	TOT ET P
TOT ET P	DLH	05/03/2013	19/02/2013	TOT ET P
TOT ET P	GET	05/03/2013	28/11/2012	TOT ET P
TOT ET P	GET	07/03/2013	28/11/2012	TOT ET P
TOT ET P	SV-CON	08/01/2013	15/12/2012	TOT ET P
TOT ET P CON	SV-CON	02/01/2013	10/12/2012	TOT
TPI	SV-CON	03/01/2013	03/12/2012	TPI
TPI	TRANS INTER	16/03/2013	10/03/2013	TPI

realpixel · ‎2013-03-28

Hello,

I try this script, it seems to work but only for the value contain in code 'LOAD * INLINE', I don't know if I use the correct command.

How to declare all value contain in column [LIEU POS] and [TRANS] in command 'LOAD * INLINE' without to fill manualy all value?

a:

LOAD * INLINE [

'LIEU POS','TRANS'

'TOT CON','SV-CONGO'

'TOT','SV-CONGO'

'TOT ET P','SV-CONGO'

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

LOAD [LIEU POS],

if([TRANS]='SV-CON' and [LIEU POS]='TOT ET P','TOT ',[LIEU POS]) as [NEW],

TRANS

Resident a order by [LIEU POS];

Related Topics