Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
in the second LOAD you should add one more field - "Cust_id". Becauce boath tables should be linked together.
Milda
Hi,
in the second LOAD you should add one more field - "Cust_id". Becauce boath tables should be linked together.
Milda
hi
attached is a solution
Thanks a lot, Milda and RoiUser!
/Anders