Hi,
what is the best tool to filter/retrieve only the latest from a certain column? I dont want to count allll of the records more than once only the katest.
I read about using summary tool? or what would be the best for this?
thanks
Hi @Hi2023
You'd want to use the summarize tool. You didn't say much about the data, but if you have a field that is a date or datetime data type, then using the 'Max' function would return the latest value. You could also play around with the 'Last' option to take the last value in a group.
https://help.alteryx.com/20223/designer/summarize-tool
Hi @Hi2023
If you have multiple columns with dates, you can also use an expression in the formula tool below.
max([Column1], [Column2])
For a single column, the Max option within the summarize tool would be the best option.
If I have mulitple groups with same acct and date (concatenated column) how do I pull only the latest from each group ?
@Hi2023 , In case of Group you can use summarize tool to pick the max and group by the column you have else you can also use sample tool if your data is in descending order and pick the first row and select the column on which you want to group by.
so I concatenate 3 columns date acct# and
24Aug2022 12:00:00 |
24Aug2022 12:00:00 |
0645540422 | 823065222 |
0645540422 | 823065222 |
then summary tool?
or use the formula to group them individually
max([egjCCT Date], [egj. CCT Date]) I used same column? since they are in same Colum now.
Hi @Hi2023
Please be informed that, in your recent scenario. Summarize tool will work and it will show the max output only one for you.
Along with that you can do the concat also.
Input:
Output:
If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.
Many thanks
Shanker V
so if I concatenate to combine 3 columns which has the date embedded in it and theres several groups it will know to grab the latest within each group
ex:
0032456-123456-2022-08-21
0032456-123456-2022-08-24
0032456-123456-2022-08-23
00333336-987654-2022-08-23
00333336-987654-2022-08-24
00333336-987654-2022-08-21
00333336-987654-2022-08-20
Would this apply for a column full of diff groups-by using summary tool max and then group by
User | Count |
---|---|
52 | |
27 | |
25 | |
24 | |
21 |