topic Re: Aggregating in Load Script in App Development

Aggregating in Load Script

Fri, 11 Nov 2016 21:12:22 GMT

Hello All,

I have reviewed several other discussions regarding this and haven't come across a solution. I am trying to create a field that will identify how many Employees a Client has in their entire Company and label it as [CompanyEmployees]. As a rule of thumb, I can have several Clients within a single Company. My data source is structured as such that we only report on the number of employees at a Client level, not at the Company level. Is it possible to accomplish this within the load script? I would then like to categorize each Client based on the [CompanyEmployees] number. eg 1-10,11-24, etc.

Client	Company	# of Employees (Client Level)	# of Employees (Company Level) TRYING TO CALC
1	1	10	20
2	1	10	20
3	2	5	25
4	2	5	25
5	2	5	25
6	2	5	25
7	2	5	25

Hopefully this makes sense.

As usual, thanks for the help.

Re: Aggregating in Load Script

Nicole-Smith — Fri, 11 Nov 2016 21:52:31 GMT

Load Script like this will give you what you have detailed in your original post:

Data:
LOAD * INLINE [
    Client, Company, # of Employees (Client Level)
    1, 1, 10
    2, 1, 10
    3, 2, 5
    4, 2, 5
    5, 2, 5
    6, 2, 5
    7, 2, 5
];

LEFT JOIN (Data)
LOAD Company,
  sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]
RESIDENT Data
GROUP BY Company;

However, I would suggest keeping the Company Level total in a separate table so that your calculations work appropriately:

Data:
LOAD * INLINE [
    Client, Company, # of Employees (Client Level)
    1, 1, 10
    2, 1, 10
    3, 2, 5
    4, 2, 5
    5, 2, 5
    6, 2, 5
    7, 2, 5
];

Company_Totals:
LOAD Company,
  sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]
RESIDENT Data
GROUP BY Company;

Doing it like the latter will make sure that if you use any sums in your calculations, you're not summing the 20 twice and the 25 five times.

Then to classify your numbers into groups, you can use the class function if you want even spaced groups:

class ‒ QlikView

If you want groups that are unevenly spaced, you will need to write if statements.

Re: Aggregating in Load Script

Fri, 18 Nov 2016 21:15:46 GMT

Thanks Nicole. I have thousands of clients and companies to calculate this for. I am pulling the data directly from a qvd. Is there a way to adjust your statement to accommodate that rather than the INLINE portion?

Re: Aggregating in Load Script

Nicole-Smith — Mon, 21 Nov 2016 14:49:58 GMT

Data:  
LOAD [Client],
  [Company],
  [# of Employees (Client Level)]
FROM YourQVD.qvd (qvd);
  
Company_Totals:  
LOAD Company,  
  sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]  
RESIDENT Data  
GROUP BY Company;

Re: Aggregating in Load Script

Mon, 28 Nov 2016 19:06:50 GMT

Hi Nicole,

This accomplished exactly what I was trying to do. Thanks for your help.

Todd