The aggregate functions seems only to work on numeric values.
How can I get the max(match_flag) per street when I have the below input data ? Do I really have to convert this match_flag field to a numeric field to achieve this?
Hi Filip, even if you had 1 instead of TRUE, the max(flag) group by street would always be 1 for each street.
Maybe what you are asking, would mean if, after converting TRUE with 1, and you after doing the sum with the aggregation, you use the Keep min / max on the match_flag column.
Reading through the thread, are you looking to keep the rows with the “TRUE” in them, and remove the “FALSE” rows? Is that the end result you’re looking for?
So, from:
street1 TRUE
street1 FALSE
street2 TRUE
street2 FALSE
to
street1 TRUE
street2 TRUE
?
If that’s correct, instead of an aggregate function, use the “Filter” action. Select the “flag” column and choose to either “Keep” the “TRUE” values, or “Remove” the “FALSE” values.
If not correct, could you describe what you want the end result of the dataset/column to look like?
What I want to achieve is that another dataset will join the above dataset on column street. And therefore, I need this alphabetical max(match_flag) value per street.
so, if I join with the below dataset :
street match_flag
, I want that the join returns me the following records
street1 TRUE
street2 TRUE
street3 FALSE
So far, I’m converting these TRUE and FALSE values into a 1 and 0 on the above dataset, to have the max(match_flag) to work. But then after the join I do a retranslate again of these 1 and 0 values back to TRUE and FALSE again as I really need the alphabetical values of this match_flag column.
I get the idea now. Yes, I believe you will need to translate T and F to 1 and 0 to accommodate the max() aggregation as Aggregate is more of a statistical calc working specifically with numerics. Text and Boolean results in “empty” cells.
You can always “Calc new column” for the 1/0 translation, do the max() on that column, then just dump the new column. (My personal approach, not liking to modify my source data.) Totally your call. Gets the same results.