topic Re: Count over several rows in QlikView

Count over several rows

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count over several rows

sunny_talwar — Thu, 09 Nov 2017 13:55:35 GMT

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'))))

Re: Count over several rows

YoussefBelloum — Thu, 09 Nov 2017 14:15:49 GMT

i think there is something missing here Sunny..

Re: Count over several rows

sunny_talwar — Thu, 09 Nov 2017 14:21:01 GMT

What is?

Re: Count over several rows

YoussefBelloum — Thu, 09 Nov 2017 14:36:17 GMT

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

Re: Count over several rows

YoussefBelloum — Thu, 09 Nov 2017 14:37:43 GMT

min takes 2 parameters

Re: Count over several rows

sunny_talwar — Thu, 09 Nov 2017 14:42:39 GMT

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

Re: Count over several rows

sunny_talwar — Thu, 09 Nov 2017 14:45:47 GMT

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'))))

Re: Count over several rows

YoussefBelloum — Thu, 09 Nov 2017 14:47:33 GMT

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

Re: Count over several rows

sunny_talwar — Thu, 09 Nov 2017 14:52:33 GMT

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;

Re: Count over several rows

YoussefBelloum — Thu, 09 Nov 2017 14:58:53 GMT

Sun is shining

thank you Sunny !

Re: Count over several rows

jessica_webb — Thu, 09 Nov 2017 16:12:41 GMT

Hi Sunny,

Thank you as always!!!

It seems to work perfectly, but I don't really understand why! Are you able to explain a bit?

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

This seems to mean that it should return 'A' if the minimum value for that ID, Group, Loc combo is 2. Which obviously isn't right. So where in the code is it specifying that this should be for every year?

Re: Count over several rows

sunny_talwar — Thu, 09 Nov 2017 16:15:55 GMT

You have two main conditions

1) Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0))

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

Create them as expression in your chart and see what happens here

Shows the minimum value based on ID, Group, Loc for all the rows grouped by ID, Group, Loc

Re: Count over several rows

jessica_webb — Fri, 10 Nov 2017 11:21:39 GMT

Hi Sunny,

I've hit upon an issue I hadn't thought of that gives me incorrect results:

ID	Group	Year	Loc	LocCount	Status
64571	10	2014	AB	1	3
64571	10	2014	BC	1	3
64571	10	2014	CD	1	3
64571	10	2014	-	5	-
64571	10	2015	DE	1	3
64571	10	2015	EF	1	3
64571	10	2015	FG	1	3
64571	10	2015	GH	1	3
64571	10	2015	HI	1	3
64571	10	2015	IJ	1	3
64571	10	2015	JK	1	3
64571	10	2015	-	1	-
64571	10	2016	KL	1	3
64571	10	2016	LM	1	3
64571	10	2016	FG	1	3
64571	10	2016	MN	1	3
64571	10	2016	NO	1	3
64571	10	2016	OP	1	3
64571	10	2016	PQ	1	3
64571	10	2016	QR	1	3
64571	10	2016	RS	1	3
64571	10	2016	ST	1	3
64571	10	2016	-	1	-

If a 'Loc' doesn't have a 'LocCount' in previous years, it doesn't show in the data. Which means each value appears to have appeared once in every year. But every year should in fact be checkign that it appears in 2013, 2014, 2015, 2016… Any ideas?

Re: Count over several rows

sunny_talwar — Fri, 10 Nov 2017 12:05:34 GMT

Are you talking about the places where you have Null or '-' Loc? Is that where the problem is?

Re: Count over several rows

jessica_webb — Fri, 10 Nov 2017 13:11:59 GMT

The table I've just posted should have 'E' as the status for all, except 'FG' as they don't have a LocCount for every year - every year being 2014, 2015 and 2016 . However, they are coming up as status 'B' (sorry for confusion in table, I swapped the statuses from letters to numbers in my code). - because the code is looking for LocCount in every year that they are listed in.

Does that make sense??

Re: Count over several rows

sunny_talwar — Fri, 10 Nov 2017 13:17:49 GMT

Let's take a step back and put down for me the conditions in words again....

Re: Count over several rows

sunny_talwar — Fri, 10 Nov 2017 13:24:33 GMT

Why is "FG" B.... FG is not available for all the years also? FG is only available in 2015 and 2016? Should it not be E also in that case?

Re: Count over several rows

jessica_webb — Fri, 10 Nov 2017 13:33:27 GMT

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 2016 and 2015

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

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

I've had to change the years - so apologies for causing a bit of confusion there.

So with the years changed in the criteria above, FG should be showing as 'B', but the rest should all be 'E'.

Re: Count over several rows

sunny_talwar — Fri, 10 Nov 2017 13:38:33 GMT

For FG to be 'B', it needs to have at least 1 in every year? Meaning all the three years (2014, 2015, 2016), right? By FG is only available in 2015 and 2016