How to use LOAD-GROUP BY?

[#1]

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

[#2 · Accepted solution]

Hi,

in the second LOAD you should add one more field - "Cust_id". Becauce boath tables should be linked together.

Milda

[lironbaram]

hi

attached is a solution

[#4]

Thanks a lot, Milda and RoiUser!

/Anders