topic repeating counter for common fields in QlikView

repeating counter for common fields

Mon, 04 Oct 2010 12:55:04 GMT

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

repeating counter for common fields

syed_muzammil — Mon, 04 Oct 2010 14:22:31 GMT

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.

repeating counter for common fields

Mon, 04 Oct 2010 14:51:57 GMT

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.

repeating counter for common fields

Wed, 06 Oct 2010 20:06:47 GMT

Thanks it works!