topic Expression: Sum column if row is unique in QlikView

Expression: Sum column if row is unique

Thu, 17 Mar 2011 15:51:29 GMT

I have three columns for arguments sake I will call them A, B, and C these fields are pulled in from a CSV file. The columns B and C need to be shown in a chart showing the following expressions

1) Sum(C)

2) The second Expression is what I am stuck on.

I need to show a sum of B but only if the row has a distinct combination of A, B and C.

Any help on where to start would be appreciated.

A: Is a workID

B: Is the total hours allocated to that workID

C: contains how many hours an employee worked on that workID

Expression: Sum column if row is unique

john_duffy — Thu, 17 Mar 2011 17:03:51 GMT

Hello.

Could you attach a sample application using inline load or give an example of the input data (actual values) and required chart.

Thanks.

Expression: Sum column if row is unique

Anonymous — Thu, 17 Mar 2011 18:01:45 GMT

The first that comes to mind:
sum(if(count({1} A&B&C)=1, B, 0))
I guess it's better to define "uniqueness" in the script rather than in the expression, by counting the number of ABC combinations, and marking as unique if count=1

Expression: Sum column if row is unique

Fri, 18 Mar 2011 09:11:00 GMT

Hi,

Thanks for the reply, here is some sample data

WOL.OpNo	WOL.StdHours	WOL.Hours	WOL.W/Order
10	100	1.25	WS61064
10	100	3.75	WS61064
10	100	8.75	WS61064
15	200	0.25	WS61064
15	200	7	WS61064
15	200	7.25	WS61064
15	200	7.5	WS61064
15	200	8	WS61064
15	200	8.5	WS61064
15	200	8.75	WS61064
15	200	9.25	WS61064
15	200	9.5	WS61064
15	200	9.75	WS61064
20	100	0.25	WS61064
20	100	0.5	WS61064
20	100	1	WS61064
20	100	1.25	WS61064
20	100	1.5	WS61064

WOL.W/Order Is the unique Job

WOL.OpNo Is an activity that makes up that job

WOL.StdHours Is the hours assigned to the OpNo

WOL.Hours Is a running total of hours worked on a OpNo

The chart needs to show a sum of WOL.Hours which is the easy part the next is what I am stuck on.

The WOL.StdHours for OpNo 10 is 100 if I simply sum it I get 300 when 100 is the correct figure in this example data I would need the graph to show 400 Hours alloted and 94 Hours taken.

The OpNo is in no way unique a differnt WOL.W/Order may contain the same OpNo.

Hope this clears things up and help you help me.

Regards

Steven

Expression: Sum column if row is unique

Fri, 18 Mar 2011 09:40:10 GMT

Is this what you want?

Regards

Anders

Expression: Sum column if row is unique

Fri, 18 Mar 2011 20:25:47 GMT

WOL.OpNo	WOL.StdHours	WOL.Hours	WOL.W/Order
10	100	1.25	WS61064
10	100	3.75	WS61064
10	100	8.75	WS61064
15	200	0.25	WS61064
15	200	7	WS61064
15	200	6	WS61064
15	200	9.75	WS61064
20	100	0.25	WS61064
20	100	.25	WS61064

Thanks for your help but unfortunately I don't think so WOL.StdHours may not always be unique and yet still needs to be counted in this example both WOL.OpNo 15 & 20 have 100 hours so 100 would need to be counted twice. The graph in this example would show 400 hours.

Expression: Sum column if row is unique

Wed, 23 Mar 2011 09:59:45 GMT

Is this what you want?

Mark sum of rows in expression tab.

Regards

Anders