repeating counter for common fields

[#1]

Hi,

This should be easy... table

Suppose I have the following:

Day	Shift Id
4-10-2010	12
4-10-2010	12
4-10-2010	14
4-10-2010	14
5-10-2010	15
5-10-2010	15
5-10-2010	15
5-10-2010	15
5-10-2010	15
5-10-2010	15

Now, I would like to have a running index on both fields. Counter that will resets itself as soon as a new subset appears.

This is the desired outcome:

Day	Shift Id	index
4-10-2010	12	1
4-10-2010	12	2
4-10-2010	14	1
4-10-2010	14	2
5-10-2010	15	1
5-10-2010	15	2
5-10-2010	15	3
5-10-2010	15	4
5-10-2010	15	5
5-10-2010	15	6

I've tried autonumber, but unfortunatly the counter does not reset itself. Is there a simple way i can tuckle this?

Dror

[syed_muzammil]

Hi,

You can try ordering the table on load on Day and Shift ID and user the peek and previous functions to increment or reset the index column.

Eg. If Previous(Shiftid)= Shiftid then Peek(Index,-1)+1 else 1.

Rgards,

Syed.

[#3 · Accepted solution]

Hi,

Syed is right, the load sentence would be as following:

Data:
LOAD
 Days,
 [Shift Id],
 if(Days = previous(Days) and [Shift Id] = previous([Shift Id]), peek('Index',-1) + 1,1) as Index
INLINE [
Days, Shift Id
4-10-2010, 12
4-10-2010, 12
4-10-2010, 14
4-10-2010, 14
5-10-2010, 15
5-10-2010, 15
5-10-2010, 15
5-10-2010, 15
5-10-2010, 15
5-10-2010, 15
];

Regards.

[#4]

Thanks it works!