topic Re: Aggr function in script, change value of a field in QlikView

Aggr function in script, change value of a field

Tue, 24 Mar 2015 16:17:17 GMT

Hello,

i have a little problem in script. I have to change a value in a field. The table looks like this:

ID	Value
1	dog
1	dog
1	cat
2	horse
2	horse
2	cat
3	parrot

So what i have to do is to sign one value to ID. So firstly i have to check if Aggr(Count(DISTIINCT Value, ID) is >0 if yes, i have to change the "cat" for ID 1 to "dog", thesame operation in ID 2. How can i do that ?

Thanks in advance!

Jacek

Re: Aggr function in script, change value of a field

jopmoekotte — Tue, 24 Mar 2015 16:30:15 GMT

Hi Jacek,

This is what you can do:

[Data_temp]:

LOAD

RecNo() as Recordnr

ID,

Value,

From YOURDATAFILE;

[Data]:

LOAD

ID,

If(ID=Peek(ID,-1),Peek(Value,-1),Value) as Value,

RESIDENT Data_temp ORDER BY Recordnr ASC;

drop table Data_temp;

drop field Recordnr;

Kind Regards,

Jop

Re: Aggr function in script, change value of a field

stigchel — Tue, 24 Mar 2015 17:33:28 GMT

You can use a where not exists clause like this:

LOAD * Inline [Id, Animal

1,dog

1,cat

2,horse

2,cat

3,parrot

] Where not Exists(Id);

Re: Aggr function in script, change value of a field

ToniKautto — Wed, 25 Mar 2015 04:21:40 GMT

I think the best solution depends a bit on your data and the actual logical reason for switching the value.

If the value switch is based on a statically incorrect value or some other predictable pattern, then I would consider using a mapping table to accomplish the value change. There are two great benefits to a mapping table; it is easy to maintain and it is efficient in execution.

In this example a possible implementation could be like below.

MapAnimal:
Mapping LOAD * Inline [
Key , Animal
1cat , dog
2cat , horse
];

T1:
LOAD 
  Id,
  ApplyMap('MapAnimal', Id&Animal, Animal) AS Animal
Inline [Id, Animal
1,dog
1,dog
1,cat
2,horse
2,horse
2,cat
3,parrot
];