How can I formulate my count function.

[Anonymous]

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?

[swuehl · Accepted solution]

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.

[Anonymous]

Hi

Excellent this worked really well

Thanks