Find Max and Insert

[Anonymous]

I have Table1 with Employee's Sales data. Need to find the Employee with highest Sales by Client ID, Category and if Year is 2015, find Employee with highest Sales by Client ID and Category in 2015 other wise find in 2014 and insert that Employee ID in Table2 for that Client ID and Category for rows where Insert is 'Y'.

For example, from Table1, 2.3, 2.5 and 1.7 are inserted into Table2.

Attached is the Excel with same entries. Need to write this in the load script.

Will really appreciate any help.

Table1:

Client ID	Client Name	Emp Name	Employee ID	$ Sales (in millions)	Category	Year
1	ClientA	John	emp1	1.54	loans	2015
1	ClientA	Peter	emp2	2.3	loans	2015
1	ClientA	Alan	emp3	4	loans	2014
1	ClientA	Peter	emp2	2.5	investment	2014
1	ClientA	Alan	emp3	2.3	investment	2014
3	ClientC	John	emp1	1.7	research	2015
3	ClientC	John	emp1	1.9	research	2014
3	ClientC	Alan	emp3	1.8	research	2014

Table2:

Client ID	Client Name	Product Type	Insert	Region	Empoyee ID
1	ClientA	loans	Y	Europe	2.3
1	ClientA	loans	N	Europe
1	ClientA	investment	Y	America	2.5
1	ClientA	investment	Y	Europe	2.5
1	ClientA	investment	Y	Asia	2.5
3	ClientC	research	N	America
3	ClientC	research	N	Asia
3	ClientC	research	Y	Europe	1.7

[anbu1984 · Accepted solution]

Table1:

LOAD [Client ID],

     [Client Name],

     [Emp Name],

     [Employee ID],

     [$ Sales (in millions)],

     Category,

     Year

FROM

[EmployeeSalesInsert.xlsx]

(ooxml, embedded labels, table is Table1);

Temp:

NoConcatenate

Load ClntId,ProdType,Max(Yr) As Yr,FirstSortedValue(EmpId,-Yr) As EmpId Group By ClntId,ProdType;

Load [Client ID] As ClntId,Category As ProdType, Year As Yr, Max([$ Sales (in millions)]) As EmpId Resident Table1 Group By [Client ID],Category, Year;

Join(Temp)

Load [Client ID] As ClntId,

     [Client Name],

     Category As ProdType,

     [$ Sales (in millions)] As EmpId

Resident Table1;

Table2:

Load *, If(IsNull(Yr),'N','Y') As Insert,If(IsNull(Yr) <> -1,EmpId) As EmployeeId Resident Temp;

Drop Table Temp;

[Anonymous]

Thank you.