Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
You can use a where not exists clause like this:
LOAD * Inline [Id, Animal
1,dog
1,dog
1,cat
2,horse
2,horse
2,cat
3,parrot
] Where not Exists(Id);
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
];