topic Re: Summing specific numerical data in load in QlikView

Summing specific numerical data in load

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

I am looking for a way to sum a set of numerical data, only when a certain condition exists. My source data has a number of personnel resources associated with a group of support types. I only want the sum of the number of resources assigned to a specific set of support types, and not others. For this example, I condensed all required support types to "dedicated" and all the rest to "not dedicated"

My source data may look like this:

Booking ID	Item Quantity	Resource
12345	1	dedicated
12345	2	dedicated
67890	2	dedicated
67890	1	not dedicated
55555	1	dedicated
11111	2	not dedicated

I want to create a new field called [Resource Count], which sums the [Item Quantity] for "Dedicated" resources. A table box of the desired data would look like this:

Booking ID	Resource Count
12345	3
67890	2
55555	1

Then, I want to create a chart showing how many Booking IDs have each Resource Count. ie- how many bookings had 2 resources, and how many had 3. The chart would have a dimension of [Resource Count] and an expression of:

count(distinct([Booking ID]))

Can you tell me how I could sum only the [Item Quantity] data associated with the 'dedicated' resource of each booking, in order to create this chart?

I have tried a preceding load:

LOAD

if(exists([field on separately loaded table listing the 5 possible 'dedicated' options],[Resource]),sum([Item Quantity])) as [Resource Count]

;

But this produced errors. I also ran a pivot table on the source Excel document which generated a list of Booking IDs and the summed Resource count I want, but when I loaded that along side of the rest of my Qlikview data, it delivered a number of nil results where there should have been a number. I also tried using left, right, inner and outer joins with this data to see if it helped (it didn't). Not to mention, I would like to avoid having to do a manual function on the source data before importing.

Re: Summing specific numerical data in load

sunny_talwar — Thu, 08 Dec 2016 20:01:33 GMT

May be like this:

DesiredTable:

LOAD [Booking ID],

Sum(If(Lower(Resource) = 'dedicated', [Item Quantity])) as [Resource Count]

Resident ....

Group By [Booking ID];

Re: Summing specific numerical data in load

sunny_talwar — Thu, 08 Dec 2016 20:04:31 GMT

Sample attached

Re: Summing specific numerical data in load

jason_nicholas — Thu, 08 Dec 2016 20:21:49 GMT

Thanks! It looks like the Resident load is the missing key. I have to do a little follow-up research to make sure I understand Resident loads- but I can handle that and don't need to hash that out on this thread.

A couple of questions:

What is the purpose of "Lower" here? Is it just because I mixed lower/upper case in my description? (an accident, and not part of the data) Or does it perform some further function?

Do you see any issues replacing { If(Lower(Resource)='dedicated', }

with my{ If(Exists...... } statement above? The actual data which "dedicated" is substituting for is a series of rather lengthy statements, and the possible Resources to be included are likely to be updated on occasion. Rather than individually listing each choice within the LOAD statement and managing it there, I would prefer to manage it on my source list, which is already serving that purpose for other functions.

Lastly, is there a way to eliminate the 0 result in the [Resource Count] field? I could just pull up a table for that field and select all non-0 options, or create a button, or otherwise take care of it post-LOAD, but if there is a clean way to do it right off the bat, it would be preferable.

Re: Summing specific numerical data in load

sunny_talwar — Thu, 08 Dec 2016 20:32:54 GMT

What is the purpose of "Lower" here? Is it just because I mixed lower/upper case in my description? (an accident, and not part of the data) Or does it perform some further function?

Yes the sole purpose of lower was to make sure that if there is a casing difference in the actual data, the if statement doesn't fall through.

Do you see any issues replacing { If(Lower(Resource)='dedicated', }

with my{ If(Exists...... } statement above? The actual data which "dedicated" is substituting for is a series of rather lengthy statements, and the possible Resources to be included are likely to be updated on occasion. Rather than individually listing each choice within the LOAD statement and managing it there, I would prefer to manage it on my source list, which is already serving that purpose for other functions.

You can use exists, but will have to be little careful. I will share another example with exist

Lastly, is there a way to eliminate the 0 result in the [Resource Count] field? I could just pull up a table for that field and select all non-0 options, or create a button, or otherwise take care of it post-LOAD, but if there is a clean way to do it right off the bat, it would be preferable.

Will propose a solution for this in the following updated sample

Re: Summing specific numerical data in load

sunny_talwar — Thu, 08 Dec 2016 20:38:19 GMT

Script

LOAD * Inline [

Check

dedicated

Dedicated

];

Table:

LOAD * INLINE [

Booking ID, Item Quantity, Resource

12345, 1, dedicated

12345, 2, Dedicated

67890, 2, dedicated

67890, 1, not dedicated

55555, 1, dedicated

11111, 2, not dedicated

];

DesiredTable:

LOAD *

Where [Resource Count] > 0;

LOAD [Booking ID],

Sum(If(Exists(Check, Resource), [Item Quantity])) as [Resource Count]

Resident Table

Group By [Booking ID];

1) Portion is green is used for Exists function. This can be loaded from a qvd or Excel file. I just did it using Inline.

2) Portion in orange uses the Exists function as you wanted

3) Portion in blue is to get rid of rows with 0 count