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!!

[sunny_talwar]

May be this

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

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

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

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

[YoussefBelloum]

i think there is something missing here Sunny..

[sunny_talwar]

What is?

[YoussefBelloum]

when i put your code inside a load, it is highlighted in red. i don't know what is wrong

[YoussefBelloum]

min takes 2 parameters

[sunny_talwar]

This is a front end expression, are you trying this in the script by any chance?

[sunny_talwar · Accepted solution]

Slight modification

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

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

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

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

[YoussefBelloum]

yes, i was already in the script typing some code for this issue, so i paste it directly.. my mistake.

if you have something like the one you posted but for the script, it will be cool

[sunny_talwar]

This should do it

Table:

LOAD * INLINE [

    ID, Group, Year, Loc, LocCount

    123, R, 2012, ABC, 3

    123, R, 2013, ABC, 5

    123, R, 2014, ABC, 2

    123, R, 2015, ABC, 3

    456, 1, 2012, ABC, 1

    456, 1, 2013, ABC, -

    456, 1, 2014, ABC, 3

    456, 1, 2015, ABC, 2

    789, R, 2012, DEF, -

    789, R, 2013, DEF, 6

    789, R, 2014, DEF, 1

    789, R, 2015, DEF, 2

    789, R, 2012, GHI, 1

    789, R, 2013, GHI, 1

    789, R, 2014, GHI, -

    789, R, 2015, GHI, -

];

Left Join (Table)

LOAD ID,

Group,

Loc,

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

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

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

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

Resident Table

Group By ID, Group, Loc;