How to make a Field Value Instance Counter?

[scotthan]

Hi community, I'm hoping to avoid a loop on this and feel like there must be a way.

Let's say I have a table like the one I've created below

 
Table:

LOAD * INLINE [
Case, StartDate, EndDate

1, 1, 2
1, 4, 5
2, 3, 4
1, 7, 8
2, 5, 6
1, 9, 9
];

I'd like to add a column to this table which is what I'm calling a "field value instance counter". This is because the same case can open (start) multiple times. For the first value of field Case, new field is 1. If the second value of Case is the same as the first, new field would be 2 on that row. Rather than trying to continue this explanation, here's what I want to get to:

Case, Start, End, FieldValueInstanceCounter

1, 1, 2, 1

1, 4, 5, 2

2, 3, 4, 1

1, 7, 8, 3

2, 5, 6, 2

1, 9, 9, 4

Any ideas?

Thanks,

Scott

[Gysbert_Wassenaar]

Temp:

LOAD * INLINE [
Case, StartDate, EndDate

1, 1, 2
1, 4, 5
2, 3, 4
1, 7, 8
2, 5, 6
1, 9, 9
];

Result:

LOAD Case, StartDate, EndDate, if(previous(Case)=Case,peek(Counter)+1,1) as Counter

resident Temp

order by Case, StartDate;

drop Table Temp;

[#3 · Accepted solution]

Hi Scott,

Using the data above I did this and think it works:

Table1:

LOAD * INLINE [
Case, StartDate, EndDate
1, 1, 2
1, 4, 5
2, 3, 4
1, 7, 8
2, 5, 6
1, 9, 9
];

Table1_tmp:
NoConcatenate
LOAD *,
if(Case = Previous(Case), Peek(FieldInstanceCounter) + 1, 1) as FieldInstanceCounter 
RESIDENT Table1
Order by Case, StartDate asc;

DROP table Table1;
RENAME table Table1_tmp to Table1;

See qvw attached. 

[#4]

Hi Scott,

Did either of these solutions work for you?

Matt

[scotthan]

I think they both work but I've used yours almost exactly Matt. Thanks!

[#6]

Glad it worked out!