Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table from external database and I need to write a correct script. Script should be summing MAUC value in max dates.
Table is now:
STORE | ITEM | DATE UPDATING | COMPONENT | MAUC |
M20 | m687587 | 04/20/2019 | 140 | € 20 |
M20 | m687587 | 04/20/2019 | 500 | € 2 |
M20 | m687587 | 31/03/2020 | 140 | 25 € |
M20 | m687587 | 31/03/2020 | 500 | € 3 |
M35 | m687587 | 02/12/2020 | 140 | 17 € |
M34 | m333333 | 02/20/2017 | 110 | 170 € |
M34 | m333333 | 02/20/2017 | 500 | 17 € |
M34 | m333333 | 08/19/2019 | 110 | 190 € |
M34 | m333333 | 08/19/2019 | 500 | 23 € |
I need FINAL table in which will be:
1. only max date updating
2. sum of MAUC according to different component
STORE | ITEM | DATE UPDATING (MAX) | MAUC |
M20 | m687587 | 31/03/2020 | 28 € |
M35 | m687587 | 02/12/2020 | 17 € |
M34 | m333333 | 08/19/2019 | € 213 |
Could you help me, how can I do this ?
Thank you.
HI, if you do an inner join with
Inner Join (DataTable)
LOAD
STORE,
ITEM,
MAX(DATE UPDATING) as DATE UPDATING
Resident DataTable
Group by STORE,ITEM;
That keeps only the rows you want.
You can do another group by of that data to crate the sum or leva as it is so also have the value of each component.
Instead of an Inner Join you can also do a left join to add a flag (just add a field like "1 as flagLastUpdate"), and use this flag in set analysis to filter las update data by store and item.
This way you keep all data and can easily do calcualtions for last update.
HI, if you do an inner join with
Inner Join (DataTable)
LOAD
STORE,
ITEM,
MAX(DATE UPDATING) as DATE UPDATING
Resident DataTable
Group by STORE,ITEM;
That keeps only the rows you want.
You can do another group by of that data to crate the sum or leva as it is so also have the value of each component.
Instead of an Inner Join you can also do a left join to add a flag (just add a field like "1 as flagLastUpdate"), and use this flag in set analysis to filter las update data by store and item.
This way you keep all data and can easily do calcualtions for last update.
thank you