Numbering unique values

[Saro_2306]

Hi all,

Data	Result
1	1
2	1
2	0
3	1
3	0
3	0
4	1
4	0
4	0

As shown in table I need to number 1 for 1st value and 0 for duplicate values. Can body help. Thanks in advance,

[edwin]

two possible ways,

you can do an if(peek(Data) =Data then this is not the first instance
or if you have a large data set, i would add a ROWNO() field; left join the min(RowNo) group by Data - that will be the first instance

[Saro_2306]

Expression for below result also useful for me.

[PradeepK]

You can do this in so many ways.. below is an example with Previous() function

Code Sample for Reference Only:

[Test]:
Load * Inline [
data_org
1
2
2
3
3
3
4
4
4
];

NoConcatenate

[Result]:
Load
	data_org,
	if(data_org = Previous(data_org),0,1) as Result;
Load
	data_org
Resident [Test]
ORDER BY data_org ASC;


Drop Table [Test];

 

Hope this helps!

[edwin]

one way:

load
if(peek(data)=data,peek(Seq)+1,1) as Seq, *;
load * inline [
data
1,1
2,1
2,0
3,1
3,0
3,0
4,1
4,0
4,0
];

[edwin]

[edwin]

however if you have a huge data set you wont want to loop and determine your sequence one row at a time:

data:
load
rowno()as rnum, data;
load * inline [
data
1,1
2,1
2,0
3,1
3,0
3,0
4,1
4,0
4,0
];

inner join (data)
load data, min(rnum) as min
Resident data group by data;

newData:
load data, rnum-min+1 as Seq
Resident data;

drop table data;

this gives you the same result but will be faster when you get to 10s of millions

[Saro_2306]

Thanks Pradeep, but above script only checks with previous value right, what if numbers are arranged in different order.

[Saro_2306]

Thanks Edwin, above script is not working when data shuffles itself.

like, 1,2,1,3,4,3,2,1.

[MayilVahanan]

Hi @Saro_2306 

Try like below

table:
LOAD * INLINE [
Data
1
2
1
3
4
3
2
1
];

Load Data, if(Data = Peek(Data), 0, 1) as Result Resident table order by Data;

DROP Table table;

Actually, order by data will sort the data column . so it will works.