topic Re: How to make a Field Value Instance Counter? in QlikView

How to make a Field Value Instance Counter?

scotthan — Thu, 06 Feb 2014 17:36:12 GMT

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

Re: How to make a Field Value Instance Counter?

Gysbert_Wassenaar — Thu, 06 Feb 2014 17:42:48 GMT

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;

Re: How to make a Field Value Instance Counter?

Thu, 06 Feb 2014 17:49:22 GMT

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.

Re: How to make a Field Value Instance Counter?

Mon, 10 Feb 2014 16:05:28 GMT

Hi Scott,

Did either of these solutions work for you?

Matt

Re: How to make a Field Value Instance Counter?

scotthan — Mon, 10 Feb 2014 16:11:16 GMT

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

Re: How to make a Field Value Instance Counter?

Mon, 10 Feb 2014 16:16:29 GMT

Glad it worked out!