Count over several rows

[jessica_webb]

Hi,

I have a rather large table with the following dimensions:

ID	Group	Year	Loc	LocCount	Status
123	R	2012	ABC	3	A
123	R	2013	ABC	5	A
123	R	2014	ABC	2	A
123	R	2015	ABC	3	A
456	1	2012	ABC	1	C
456	1	2013	ABC	-	C
456	1	2014	ABC	3	C
456	1	2015	ABC	2	C
789	R	2012	DEF	-	D
789	R	2013	DEF	6	D
789	R	2014	DEF	1	D
789	R	2015	DEF	2	D
789	R	2012	GHI	1	E
789	R	2013	GHI	1	E
789	R	2014	GHI	-	E
789	R	2015	GHI	-	E

From this, I want an expression for the 'Status' column using the following criteria (in the hierarchical order shown below):

A - If the LocCount for the ID, Group and Loc is at least 2 in every year

B - If the LocCount for the ID, Group and Loc is at least 1 in every year

C - If the LocCount for the ID, Group and Loc is at least 2 in 2015 and 2014

D - If the LocCount for the ID, Group and Loc is at least 1 in 2015 and 2014

E - If above criteria not met for any ID, Group and Loc

I've completed the status column in the example above with what I would expect to see.

What's the best way to achieve this? In the script or by expression only?

Help would be really appreciated on this as I'm on a very tight schedule and not sure where to start!!

[jessica_webb]

Sorry Sunny - doing a rubbish job at multitasking today!

Ok, FG should be 'D' - there is at least 1 in 2016 and 1 in 2015

[sunny_talwar]

Try this

If(Count(TOTAL <ID, Group, Loc>Year) = 3,

    If(Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0)) = 2, 'A',

    If(Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0)) = 1, 'B', 'E')),

If(Count(TOTAL <ID, Group, Loc>{<Year = {2016, 2015}>}Year) = 2,

    If(Min(TOTAL <ID, Group, Loc> {<Year = {2016, 2015}>} Alt(LocCount, 0)) = 2, 'C',

    If(Min(TOTAL <ID, Group, Loc> {<Year = {2016, 2015}>} Alt(LocCount, 0)) = 1, 'D', 'E')), 'E'))

For when Loc = '-', we have three years, so it falls under status 'B'

[jessica_webb]

Fantastic

You have saved my weekend!!!

I'm struggling to get it working in the script though. I'm using:

LEFT Join (TABLE)

LOAD

ID,

Group,

Loc,,

LocCount

Year,

If(Count(TOTAL <ID, Group, Loc>Year) = 3,

    If(Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0)) = 2, 'A',

    If(Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0)) = 1, 'B', 'E')),

If(Count(TOTAL <ID, Group, Loc>{<Year = {2016, 2015}>}Year) = 2,

    If(Min(TOTAL <ID, Group, Loc> {<Year = {2016, 2015}>} Alt(LocCount, 0)) = 2, 'C',

    If(Min(TOTAL <ID, Group, Loc> {<Year = {2016, 2015}>} Alt(LocCount, 0)) = 1, 'D', 'E')), 'E')) as Status

Resident TABLE

Group By ID, Group, Loc;

[sunny_talwar]

TOTAL doesn't work in the script ....

[sunny_talwar]

Try this

Left Join (Table)

LOAD ID,

Group,

Loc,

If(Count(Year) = 3,

If(Min(Alt(LocCount, 0)) = 2, 'A',

If(Min(Alt(LocCount, 0)) = 1, 'B', 'E')),

If(Count(If(Match(Year, 2016, 2015), Year)) = 2,

If(Min(If(Match(Year, 2016, 2015), Alt(LocCount, 0))) = 2, 'C',

If(Min(If(Match(Year, 2016, 2015), Alt(LocCount, 0))) = 1, 'D', 'E')), 'E')) as Status

Resident Table

Group By ID, Group, Loc;