topic Re: Flag repeted values in a field at script level in QlikView

Flag repeted values in a field at script level

bvmike2001 — Wed, 02 Mar 2016 12:17:19 GMT

Hello,

i have a simple data set like this:

ID Value

1 A

2 B

1 A

4 C

5 B

6 D

7 E

8 C

9 D

what best or efficient solution should i use in order to calculate at the script level a new column in witch i want to mark if a Value is repeated or not in next rows and having different IDs?

what i expect is:

ID Value Repeated_Flag

1 A 0

2 B 1

1 A 0

4 C 1

5 B 0

6 D 1

7 E 0

8 C 0

9 D 0

Thanks!

Mihai

Re: Flag repeted values in a field at script level

Gysbert_Wassenaar — Wed, 02 Mar 2016 16:38:52 GMT

Something like this:

Temp:

LOAD *, RecNo() as RecNo INLINE [

ID, Value

1 , A

2 , B

1 , A

4 , C

5 , B

6 , D

7 , E

8 , C

9 , D

];

Result:

LOAD

RecNo,

ID,

Value as CheckVal1,

ID&Value as CheckVal2,

Value,

IF(Exists(CheckVal1,Value) and Not Exists(CheckVal2,ID&Value) ,1,0) as Repeated_Flag

RESIDENT

Temp

ORDER BY

ID desc

;

DROP TABLE Temp;

DROP FIELD CheckVal1, CheckVal2;

Re: Flag repeted values in a field at script level

bvmike2001 — Wed, 02 Mar 2016 17:44:43 GMT

Hello Gysbert,

yes your solution works and deliver expected solution.

Meanwhile i have found myself a solution using inner join on Value and then testing the IDs.

Many Thanks!