Count the occurrence of field from another table

[#1]

Hello,

I have tables Table A and Table B. Need to create Table C  as given below.  Appreciate any help with this,

[sunny_talwar]

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;

[sunny_talwar]

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;