Aggregating in Load Script

[#1]

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.

TJ

[Nicole-Smith]

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.

[#3]

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?

[Nicole-Smith · Accepted solution]

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; 

[#5]

Hi Nicole,

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

Todd