Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have tables Table A and Table B. Need to create Table C as given below. Appreciate any help with this,
May be this:
TableA:
LOAD * INLINE [
Sales Person, Salary
A, 40000
B, 20000
C, 30000
D, 20000
E, 30000
F, 30000
];
TableB:
LOAD * INLINE [
Date, Order ID, Order Amount, Sales Person
03/09/2016, a12345, 500, A
04/09/2016, a12346, 200, B
05/09/2016, a12347, 600, A
06/09/2016, a12348, 300, B
07/09/2016, a12349, 200, D
08/09/2016, a12350, 200, A
];
TableCTemp:
NoConcatenate
LOAD *
Resident TableA;
Left Join (TableCTemp)
LOAD [Sales Person],
Count([Sales Person]) as [No of Sales]
Resident TableB
Group By [Sales Person];
TableC:
NoConcatenate
LOAD [Sales Person],
Salary,
Alt([No of Sales], 0) as [No of Sales]
Resident TableCTemp;
DROP Table TableCTemp;
Or using Mapping Load:
TableA:
LOAD * INLINE [
Sales Person, Salary
A, 40000
B, 20000
C, 30000
D, 20000
E, 30000
F, 30000
];
TableB:
LOAD * INLINE [
Date, Order ID, Order Amount, Sales Person
03/09/2016, a12345, 500, A
04/09/2016, a12346, 200, B
05/09/2016, a12347, 600, A
06/09/2016, a12348, 300, B
07/09/2016, a12349, 200, D
08/09/2016, a12350, 200, A
];
MappingLoad:
Mapping
LOAD [Sales Person],
Count([Sales Person]) as [No of Sales]
Resident TableB
Group By [Sales Person];
TableC:
LOAD [Sales Person],
Salary,
ApplyMap('MappingLoad', [Sales Person], 0) as [No of Sales]
Resident TableA;