Choose maxdate value of MAUC

[jozisvk11]

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.

[rubenmarin · Accepted solution]

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.

[jozisvk11]

thank you