Announcements
cancel
Showing results for
Did you mean:
Not applicable

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

1 Solution

Accepted Solutions
MVP
```Data:
[Company],
[# of Employees (Client Level)]
FROM YourQVD.qvd (qvd);

Company_Totals:
sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]
RESIDENT Data
GROUP BY Company;
```
4 Replies
MVP

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

```Data:
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)
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:
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:
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.

Not applicable
Author

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?

MVP
```Data:
[Company],
[# of Employees (Client Level)]
FROM YourQVD.qvd (qvd);

Company_Totals: