Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Try this:
LOAD
Employee,
Maxstring(SalePrimaryKey), as SalePrimaryKey,
FirstSortedValue(Sales) as Sales,
FirstSortedValue(Count) as Count
GROUP BY
Employee;
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?
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;
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 |
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;
This keeps returning a syntax error, missing/misplaced FROM:
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
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.