Filtering rows based on max value in a column

[JochenF]

HI Folks

I am a Newbie in using Alteryx and couldn't find the solution within the community atricles.

I import an Excel on a weekly base to get most actual data, but I just need the last load within each month. The latest monthly load is done on 1st of the following month.

The tabel looks like:

Col1	...	ColX	Month (string yyyymm)	LoadDate (date)
a	b	c	202010	1-11-2020
d	e	f	202010	1-11-2020
g	h	i	202011	7-11-2020
j	k	l	202011	7-11-2020
m	n	o	202011	1-12-2020
p	q	r	202011	1-12-2020

Waht I want now as a result is keeping all month > max(month)  and all max(month) = max(load) date. Means all rows from load date 7-11-2020 should be deleted

I found in the community that using max doesn't work within the filter tool. I also found that the summarize tool get's max value of a column ... but I have no idea how to combine it to get the wanted result of if there is a completely different way.

Looking forward to your answers.

Best regards, Jochen

[PhilipMannering]

If you just want Load Date to be the first of the month, the simplest solution might be,

See attached

Philip

[atcodedog05 · Accepted solution]

Hi @JochenF 

The workflow you are speaking about will look something like this.

But i am not clear with condition.

Workflow:

Hope this helps 🙂

[JochenF]

Thanks, this works pretty fine and now I better understand how the filter tool works in combination with criteria found in columns. 

[atcodedog05]

Happy to help 🙂 @JochenF 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂