Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello experts,
I'm having some struggle on cleaning some duplicates. I think a group by can solve this issue but I don't know how to approach. So I've got this data set, when duplicates are considered when all but "load date" are the same.
OPERATIONS:
load * Inline[
Load Date, Operation Date, Department, Product, Value
17/01/2020, 10/01/2020, Regulatory, Snickers, 100
15/01/2020, 10/01/2020, Regulatory, Snickers, 100
13/01/2020, 10/01/2020, Regulatory, Snickers, 100
16/01/2020, 10/01/2020, Finance, Kitkat, 100
15/01/2020, 10/01/2020, Finance, Kitkat, 100
13/01/2020, 10/01/2020, Finance, Kitkat, 100
];
So the result I need should be:
Load Date, Operation Date, Department, Product, Value
17/01/2020, 10/01/2020, Regulatory, Snickers, 100
16/01/2020, 10/01/2020, Finance, Kitkat, 100
I need the max load date for each of these duplicates.
Thanks in advance
OPERATIONS:
load * Inline [
Load Date, Operation Date, Department, Product, Value
17/01/2020, 10/01/2020, Regulatory, Snickers, 100
15/01/2020, 10/01/2020, Regulatory, Snickers, 100
13/01/2020, 10/01/2020, Regulatory, Snickers, 100
16/01/2020, 10/01/2020, Finance, Kitkat, 100
15/01/2020, 10/01/2020, Finance, Kitkat, 100
13/01/2020, 10/01/2020, Finance, Kitkat, 100
];
Final:
NoConcatenate
Load max([Load Date]) as [Load Date],
[Operation Date],
Department,
Product,
Value
Resident OPERATIONS
Group by
[Operation Date],
Department,
Product,
Value;
Drop Table OPERATIONS;
OPERATIONS:
load * Inline [
Load Date, Operation Date, Department, Product, Value
17/01/2020, 10/01/2020, Regulatory, Snickers, 100
15/01/2020, 10/01/2020, Regulatory, Snickers, 100
13/01/2020, 10/01/2020, Regulatory, Snickers, 100
16/01/2020, 10/01/2020, Finance, Kitkat, 100
15/01/2020, 10/01/2020, Finance, Kitkat, 100
13/01/2020, 10/01/2020, Finance, Kitkat, 100
];
Final:
NoConcatenate
Load max([Load Date]) as [Load Date],
[Operation Date],
Department,
Product,
Value
Resident OPERATIONS
Group by
[Operation Date],
Department,
Product,
Value;
Drop Table OPERATIONS;
Thanks Kush, it works!. Just one thing, do I need to group by every dimension of the Final table? Is this always the case?
Thanks
Yes. You need to include all the dimensions in group by which you have included in load statement.