Hi,
I'm trying to use the LOAD ... GROUP BY command without any success..
In the simplified example below, there are 3 customers with 1000, 2000 and 3000 as Customer_id. Each of them owns one or more cars represented by a uniqe Reg_no. What I want to create is a field holding the total amount of owned cars for each customer, something like this:
Cust_id Number of cars
1000 7
2000 3
3000 5
But I got a table box like:
| Cust_id | Number of cars |
| 1000 | 7 |
| 1000 | 3 |
| 1000 | 5 |
| 2000 | 7 |
| 2000 | 3 |
| 2000 | 5 |
| 3000 | 7 |
| 3000 | 3 |
| 3000 | 5 |
----------
Here is my load script (se also attached qvw-file) :
Data:
LOAD * INLINE [
Make,Reg_no,Cust_id
Ford,AB123,1000
Toyota,AC123,1000
BMW,AD123,1000
Mercedes,AE123,1000
Ford,AF123,1000
BMW,AG123,1000
Ford,AH123,1000
Toyota,AJ123,2000
Ford,AK123,2000
Ford,AB124,2000
Toyota,AC124,3000
BMW,AD124,3000
Mercedes,AE124,3000
Ford,AF124,3000
BMW,AG124,3000
];
LOAD COUNT(Distinct(Reg_no)) AS 'Number of cars' RESIDENT Data GROUP BY Cust_id
----------
All help is very much appreciated!
/Anders
