topic Re: Find and Replace function in QlikView

Find and Replace function

realpixel — Wed, 20 Mar 2013 17:44:06 GMT

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

Re: Find and Replace function

Thu, 21 Mar 2013 06:09:09 GMT

try this

LOAD * INLINE [

CUSTOMER

customer1

customer2

customer3

customer4

customer5

customer6

customer7

customer8

customer9

customer10

customer11

customer12

];

LOAD CUSTOMER,

Pick(

Match(CUSTOMER,'customer1','customer2','customer3',

'customer4','customer5','customer6',

'customer7','customer8','customer9'),

'customer1','customer1','customer1',

'customer4','customer4','customer4',

'customer7','customer7','customer7') as CUSTOMER_new

Resident a;

DROP Table a;

Re: Find and Replace function

realpixel — Thu, 21 Mar 2013 08:16:12 GMT

Thank you for your reply, but I have forgot something

The column [CUSTOMER] and [CODE] contain 12100 rows.

I need to do this operation.

If column [CODE] contain the value A, customer1, customer2 and customer3 must be replaced by customer1

If column [CODE] contain the value A, customer4, customer5 and customer6 must be replaced by customer4

If column [CODE] contain the value A, customer7, customer8 and customer9 must be replaced by customer7

[CUSTOMER]

customer1 A

customer2 A

customer3 A

customer4 B

customer5 B

customer6 C

customer7 D

customer8 D

customer9 D

customer10 E

customer11 E

customer12 E

Re: Find and Replace function

perumal_41 — Thu, 21 Mar 2013 08:30:05 GMT

use this script

LOAD * INLINE [

CUSTOMER,Code

customer1,A

customer2,A

customer3,A

customer4,B

customer5,B

customer6,C

customer7,D

customer8,D

customer9,D

customer10,E

customer11,E

customer12,E];

LOAD CUSTOMER,

if(Code=peek(Code),Peek(CUSTOMER1),CUSTOMER) as CUSTOMER1,

Code

Resident a order by CUSTOMER;

DROP Table a;

Re: Find and Replace function

realpixel — Thu, 21 Mar 2013 09:38:11 GMT

I try the code under wthout success.

[LIEU POS] [TRANS]

TOT CON SV-CON

TOT ET P SV-CON

TOT ET P CON SV-CON

customer4 B

customer5 B

customer6 C

customer7 D

customer8 D

customer9 D

customer10 E

customer11 E

customer12 E

LOAD * INLINE [

'LIEU POS','TRANS'

'TOT CON','SV-CON'

'TOT ET P','SV-CON'

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

LOAD 'LIEU POS',

if(TRANS)=peek(TRANS),Peek(TOTAL CONGO),'LIEU POS') as 'TOT CON',

Resident a order by 'LIEU POS';

DROP Table a;

Re: Find and Replace function

perumal_41 — Thu, 21 Mar 2013 10:05:44 GMT

Now try

[LIEU POS] [TRANS]

TOT CON SV-CON

TOT ET P SV-CON

TOT ET P CON SV-CON

customer4 B

customer5 B

customer6 C

customer7 D

customer8 D

customer9 D

customer10 E

customer11 E

customer12 E

LOAD * INLINE [

'LIEU POS','TRANS'

'TOT CON','SV-CON'

'TOT ET P','SV-CON'

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

LOAD 'LIEU POS',

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

Resident a order by 'LIEU POS';

DROP Table a;

Re: Find and Replace function

realpixel — Thu, 21 Mar 2013 10:31:27 GMT

Error in expression:

If takes 2-3 parameters

LOAD 'LIEU POS',

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

Resident a order by 'LIEU POS';

Re: Find and Replace function

perumal_41 — Thu, 21 Mar 2013 10:41:42 GMT

TRy this Expression

LOAD * INLINE [

LIEU POS ,TRANS

customer1,A

customer2,A

customer3,A

customer4,B

customer5,B

customer6,C

customer7,D

customer8,D

customer9,D

customer10,E

customer11,E

customer12,E];

//LOAD CUSTOMER,

// if(Code=peek(Code),Peek(CUSTOMER1),CUSTOMER) as CUSTOMER1,

// Code

//Resident a order by CUSTOMER;

//DROP Table a;

LOAD [LIEU POS],

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

TRANS

Resident a order by [LIEU POS];

DROP Table a;

Re: Find and Replace function

realpixel — Thu, 21 Mar 2013 11:53:44 GMT

Thank Perumal A for your assistance, the script seems to work but I don't understand something.

why table 'a' and 'a-1' are created when I apply this script? Table 'a' return no information and table 'a-1' return 'TO CON'

I try to explain, this script create a field [TOT CON] which contain the result of converting 'TOT ET P' and 'TOT ET P CON' to 'TON CON' but these other value contain in [LIEU POS] (there is 12100 rows) not appears in the field [TOT CON].

I need that the othe value contain in [LIEU POS] are transfer to [TOT CON] without modification.

I hope that these explanation are clear

Re: Find and Replace function

perumal_41 — Thu, 21 Mar 2013 13:24:40 GMT

is possible little briefly explain ,I couldn’t understand what your requirements.

Regards

Perumal A

Re: Find and Replace function

realpixel — Thu, 21 Mar 2013 14:13:21 GMT

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;

Re: Find and Replace function

Thu, 21 Mar 2013 14:39:33 GMT

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)

Re: Find and Replace function

realpixel — Thu, 21 Mar 2013 15:58:39 GMT

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

Re: Find and Replace function

realpixel — Sat, 23 Mar 2013 08:35:35 GMT

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.

Re: Find and Replace function

realpixel — Sat, 23 Mar 2013 10:00:14 GMT

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

Re: Find and Replace function

CELAMBARASAN — Sat, 23 Mar 2013 11:17:17 GMT

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?

Re: Find and Replace function

realpixel — Mon, 25 Mar 2013 07:44:51 GMT

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.

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.

Re: Find and Replace function

CELAMBARASAN — Mon, 25 Mar 2013 08:00:27 GMT

I misunderstood ur requirements.

Check with this

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

Re: Find and Replace function

realpixel — Mon, 25 Mar 2013 08:34:53 GMT

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.

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

Re: Find and Replace function

CELAMBARASAN — Mon, 25 Mar 2013 13:11:36 GMT

Do you need TRANS in that place?

means

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

Regards,

Celambarasan