topic How can I formulate my count function. in QlikView

How can I formulate my count function.

Anonymous — Mon, 12 Aug 2013 07:58:36 GMT

I have a set of data which has a unique reference, how ever this data is liable to change.

this is my current expression

COUNT({$ <Area ={'Foreign Body'}, Sub_Area ={'Bone'}>} Distinct [SR_Ref])

This works ok unless the area and sub area data changes then it double counts the SR_Ref.

There is a filedate field is unique to each SR-Ref line. which if i can count the line of data with the last filedate i believe it may work but i do not know how to formulate the expression.

thought may be i could use count(if ?

Please hcan anyone help?

Re: How can I formulate my count function.

swuehl — Mon, 12 Aug 2013 09:26:41 GMT

You could create an additional field in the script that flags the most recent record per SR_Ref.

There are multiple ways to do this, e.g.

TABLE1:

LOAD SR_Ref,

FileDate,

Area,

Sub_Area

FROM ...;

LEFT JOIN (TABLE1)

LOAD SR_Ref,

Date(max(FileDate)) as FileDate,

1 as Flag

RESIDENT TABLE1 GROUP BY SR_Ref;

or a MAPPING approach should also work.

Then add Flag = {1} to your set expression field selections.

Re: How can I formulate my count function.

Anonymous — Wed, 11 Sep 2013 12:39:11 GMT

Excellent this worked really well

Thanks