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,

[MayilVahanan]

Hi @Saro_2306 

For this,

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

Try like above, Result2 give solution

[edwin]

it obviously has to be sorted.  you need to supply more info.  it could not be possible that you only have one column.  you should order depending on your business rules.  what fields are you sorting it by?  is there a date field?  is it based on the DATA field and load order?

here is a sample, if there are multiple fields but you do not want it re-ordered and still want the sequence based on DATA field:

load your data with a row no - this preserves the load order.  then add a second row no order by data field and 1st row no.  this way you can use the logic to sequence the data based on load order.

at this point you can use the peek function or the table operation (choice would depend on whether you have a huge data set.  if you use the peek function and have your data in the high 10 millions you will  realize peek is very slow 

[PradeepK]

maybe you missed this line in provided solution

ORDER BY data_org ASC;

In case you have sorting restriction and only want to work with data load order.. then it can be easily done with Mapping load and Recno().

Let me know if you have any other doubts.

[PradeepK]

Use this if you have sorting constraints ... 

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


Map_Test:
Mapping Load
	data_org,
    Recno() as pos 
Resident [Test];

[Result]:
Load
	data_org,
    if( Recno() = ApplyMap('Map_Test',data_org),1,0) as Result
Resident [Test];



Drop Table [Test];

Hope this helps!