Maxstring and Distinct

[#1]

I have a table with 4 columns, I need to know how to do something in the load script like the following.

MaxString(SalePrimaryKey), Distinct(Employee), Sales, Count. Does anyone have any ideas?

[cesaraccardi]

Try this:

LOAD

     Employee,

     Maxstring(SalePrimaryKey), as SalePrimaryKey,

     FirstSortedValue(Sales) as Sales,

     FirstSortedValue(Count) as Count

GROUP BY

     Employee;

[sunny_talwar]

Do you mean FirstValue instead of FirstSortedValue? FirstSortedValue takes two inputs:

LOAD

     Employee,

     Maxstring(SalePrimaryKey), as SalePrimaryKey,

     FirstValue(Sales) as Sales,

     FirstValue(Count) as Count

GROUP BY

     Employee

But somehow the above doesn't seem right as we are not sorting the table. Zeth would you be able to share some sample data with expected output?

[cesaraccardi]

Yes, thats right thanks. I meant FirstValue(), alternatively those fields could be listed on the group by clause:

LOAD

     Employee,

     Maxstring(SalePrimaryKey), as SalePrimaryKey,

     Sales,

     Count

GROUP BY

     Employee, Sales, Count;

[#5]

This is the initial data, you can see employeeID 546 is duplicated. I want to return the Employee ID with the greatest SalePrimary Key.

SalePrimaryKey	EmployeeID	Sales	Count
1	546	500	7
2	128	1000	9
3	546	1250	5
4	896	50	1
5	452	1583	84
6	546	1540	4
7	236	1531	2

So it would return the following. 

SalePrimaryKey	EmployeeID	Sales	Count
2	128	1000	9
4	896	50	1
5	452	1583	84
6	546	1540	4
7	236	1531	2

[sunny_talwar]

May be this:

Table:

LOAD * Inline [

SalePrimaryKey, EmployeeID, Sales, Count

1, 546, 500, 7

2, 128, 1000, 9

3, 546, 1250, 5

4, 896, 50, 1

5, 452, 1583, 84

6, 546, 1540, 4

7, 236, 1531, 2

];

FinalTable:

NoConcatenate

LOAD EmployeeID,

  FirstSortedValue(SalePrimaryKey, -SalePrimaryKey) as SalePrimaryKey,

  FirstSortedValue(Sales, -SalePrimaryKey) as Sales,

  FirstSortedValue(Count, -SalePrimaryKey) as Count

Resident Table

Group By EmployeeID;

DROP Table Table;

[#7]

This keeps returning a syntax error, missing/misplaced FROM:

[sunny_talwar]

I won't be able to see the same error, because I am using an inline load. Would you be able to share your script and the exact error that you are seeing???

Best,

Sunny

[jagan · Accepted solution]

HI,

Try Inner Join using the simple script below

Table:

LOAD * Inline [

SalePrimaryKey, EmployeeID, Sales, Count

1, 546, 500, 7

2, 128, 1000, 9

3, 546, 1250, 5

4, 896, 50, 1

5, 452, 1583, 84

6, 546, 1540, 4

7, 236, 1531, 2

];

INNER JOIN(Table)

LOAD EmployeeID,

Max(SalePrimaryKey)

Resident Table

Group By EmployeeID;

Hope this helps you.

Regards,

Jagan.