How to find Max and Min when you have missing value ?

am using this query but it is returning NULL for minimum

You can use a NullAttributeMapper to map those missing values to something else (but what that should be is something you need to decide...)

The overall idea is to obtain maximum and minimum value out. If those 6 columns irrespective of they are missing or having some other value or not .

Even if there are more than 2 values it should check the values in each cell and output the maximum and minimum.

I shall highly appreciate if u cld provide me steps in more details considering that am learning FME .

thank you so much

You could do something like this in an InlineQuerier, choosing to coalesce to an appropriate large value to use for min and a small value to use for max depending on your data

Building on the idea of @ebygomm​, where you use a predefined value that is guaranteed to be higher than your highest value, it is possible to do this in an AttributeManager (or AttributeCreator) using math operator x?y:z (if-then-else).

It does not look pretty, but something like the formula below works.

@min((@StringLength(@Value(Attr1))==0)?999:@Evaluate(@Value(Attr1)),(@StringLength(@Value(Attr2))==0)?999:@Evaluate(@Value(Attr2)),(@StringLength(@Value(Attr3))==0)?999:@Evaluate(@Value(Attr3)),(@StringLength(@Value(Attr4))==0)?999:@Evaluate(@Value(Attr4)),(@StringLength(@Value(Attr5))==0)?999:@Evaluate(@Value(Attr5)))

A short explanation: If the length of an attribute is zero, use 999, otherwise use the value of the attribute.

 This is only needed for @min, the value of @max is calculated correctly.

@min((@StringLength(@Value(Attr1))==0)?999:@Evaluate(@Value(Attr1)),(@StringLength(@Value(Attr2))==0)?999:@Evaluate(@Value(Attr2)),(@StringLength(@Value(Attr3))==0)?999:@Evaluate(@Value(Attr3)),(@StringLength(@Value(Attr4))==0)?999:@Evaluate(@Value(Attr4)),(@StringLength(@Value(Attr5))==0)?999:@Evaluate(@Value(Attr5)))

Excellent that works so awesome :)

I am highly grateful and thankful @geomancer​ and @ebygomm​ for your quick  response and providing so detailed steps. 

Just a quick, if I wish to add Prefix that is constant would that be like

“N” or “S” or “E” or “W” etc

would it like

min((“N” @StringLength(@Value(Attr1))==0)?999:@Evaluate(@Value(Attr1)),…..

max(“N”@….

With each values there are direction associated with it, so after or before finding the max and min I wish to add Prefix of each values such that it’s like eg

N 0’7

w 0.98

S 0 . 55

Place the prefix outside of the @min or @max function.

N @max(...

Also make sure you use the Text Editor, not the Arithmatic Editor.

N @max(...

Nops its not getting through @geomancer​  

For MIN 

@min((@StringLength(S@Value(TFS))==0)?999:@Evaluate(@Value(TFS)),(N@StringLength(@Value(TFN))==0)?999:@Evaluate(@Value(TFN)),(E@StringLength(@Value(TFE))==0)?999:@Evaluate(@Value(TFE)),(W@StringLength(@Value(TFW))==0)?999:@Evaluate(@Value(TFW)),(D@StringLength(@Value(TFD))==0)?999:@Evaluate(@Value(TFD)),(U@StringLength(@Value(TFU))==0)?999:@Evaluate(@Value(TFU)))

For Max 

@max(S@Value(TFS),N@Value(TFN),E@Value(TFE),W@Value(TFW),U@Value(TFU),D@Value(TFD))

N @max(...

Just a bit more help and understanding this may help 

N @max(...

You put the strings inside the min and max function, that will not work.

The min and max function should only calculate the minimum and maximum value, ignoring the missing values.

@min((@StringLength(@Value(TFS))==0)?999:@Evaluate(@Value(TFS)),(@StringLength(@Value(TFN))==0)?999:@Evaluate(@Value(TFN)),(@StringLength(@Value(TFE))==0)?999:@Evaluate(@Value(TFE)),(@StringLength(@Value(TFW))==0)?999:@Evaluate(@Value(TFW)),(@StringLength(@Value(TFD))==0)?999:@Evaluate(@Value(TFD)),(@StringLength(@Value(TFU))==0)?999:@Evaluate(@Value(TFU)))
@max(@Value(TFS),@Value(TFN),@Value(TFE),@Value(TFW),@Value(TFU),@Value(TFD))

After that you can determine the string like in your last post.

@geomancer​ 

the workflow and results were not right for me , so had to change the method.

What i am looking now is 1st two(2) minimum values out of each records (row)

like

1st Min 2ndMin

30.00 59.99

44.99 45.00

Sorry to hear the solution was not sufficient.

As it's monday morning, and I am feeling creative, I came up with an unorthodox solution (which has been used before on the forum, I am just borrowing that idea).

Treat the values of your variables as X-coordinates of points along a line, and find the 2 points closest to 0. This assumes you only have positive values. Missing values are allowed, they will simply be skipped.

As you create new geometries, extract and later restore the original geometry.

@geomancer​ this is so brilliant thank you million times and once again, i was worried for ...what if there were 1st two same minimum , would it skip to next or keep the same , BUT here i could see from your example that it does retain same value e.g. 45 value , so its does that smart things 🙂 .

Would it also handle alphanumeric for each value as well ? for example each values have Direction (U, D, N , E, W ,S ) header associated with it . So whatever maybe the 1st two minimum values it also take the default Direction with it .

Also i noticed vertex creator U is missing , is it just typo error or for some reason ? In meantime i shall try to add this into my workbench and test results / tnx once again

1st Min

30

45

12

2nd Min

60

45

33.33

1st Min_Dir

E

W

E

2nd Min_Dir

S

N(*)

N

FINAL_expected_Output

E 30 S

W 45 N

E 12 N

*to be careful here is even though 1st min and 2nd min is same its should extract its respective Direction and not the same i.e. 45 45 W W OR N N as this would be incorrect

Hi @vimva679​ ,

Sure, just add an attribute for the Direction, add it to the list in the Neighborfinder, and get the value in the AttributeManager. I also added D for completeness.

In all modesty, this is not my brilliance. I just reused a smart idea from other users. Learning from the ideas of others is one of the great things about this community. Which is also why I want to give back by using others.

Perfect @ its exactly right now this time 🙏 thank you @geomancer​ 

You're welcome :)

@min((@StringLength(@Value(Attr1))==0)?999:@Evaluate(@Value(Attr1)),(@StringLength(@Value(Attr2))==0)?999:@Evaluate(@Value(Attr2)),(@StringLength(@Value(Attr3))==0)?999:@Evaluate(@Value(Attr3)),(@StringLength(@Value(Attr4))==0)?999:@Evaluate(@Value(Attr4)),(@StringLength(@Value(Attr5))==0)?999:@Evaluate(@Value(Attr5)))

Hi @geomancer !

Just found your solution for my problem. I tried to adapt your formula to my attributes but somehow it returns as min value 0

The formula i am using is the following:

@min((@StringLength(@Value(Adresse_{1}))==0)?999:@Evaluate(@Value(Adresse_{1})),(@StringLength(@Value(Adresse_{2}))==0)?999:@Evaluate(@Value(Adresse_{2})),(@StringLength(@Value(Adresse_{3}))==0)?999:@Evaluate(@Value(Adresse_{3})),(@StringLength(@Value(Adresse_{4}))==0)?999:@Evaluate(@Value(Adresse_{4})),(@StringLength(@Value(Adresse_{5}))==0)?999:@Evaluate(@Value(Adresse_{5})))

Do you have any idea why the result is 0 and not 16 ?

I am always happy to see re-use of old solutions :)

But this is very strange...

When I create a test with your values,and enter your formula, it gives 16 as the minimum value, as expected. So I can’t reproduce the result 0.

You are right! I manually rebuilt the dataset and the result was 16.

I’m not shure wheter it has to do with the datatype. I tried with number and char, both worked.

The dataset i am working with was derived from a pdf an processed with different transformers. The datatype of the ‘adresse{}’-attributes is varchar. Maybe it is because of this. I have no idea…

Somehow the stringlength of the missing value delivered “4”. Therefore it didn’t work.

I included a conditional value: if missing value set value 9999 in the attributemanager and it worked.

thanks again for the prior solution! Helped a lot!