Aggregates on character fields

Support
1

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?

street match_flag

street1 TRUE
street1 FALSE
street2 TRUE
street2 FALSE

2

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.

For example, starting with these data:

data value
street1 1
street1 4
street2 1

Sum value group by data:

data value
street1 5
street2 1

Keep min/max in value column

data value
street1 5

Hope I helped you...

3

So, there is no way at all to directly get the ‘TRUE’ value when trying to do a max(match_flag) from the above list?

It would be handier for me that a max(match_flag) of the street column would return TRUE instead of a numeric value. Any way how I can achieve this?

4

Hi, Filip.

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?

Craig

5

Hi,

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

street1 TRUE
street1 FALSE
street2 TRUE
street2 FALSE
street3 FALSE
street3 FALSE
street3 FALSE

, 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.

6

Hi, Filip.

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.