Solved

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


Userlevel 2
Badge +8

image

icon

Best answer by geomancer 19 June 2023, 11:57

View original

23 replies

Userlevel 2
Badge +8

am using this query but it is returning NULL for minimumimage

Userlevel 5
Badge +32

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

Userlevel 2
Badge +8

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

Userlevel 2
Badge +21

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

 

image

Userlevel 5
Badge +40

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.

Userlevel 2
Badge +8

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.

Excellent that works so awesome :)

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

 

image

Userlevel 2
Badge +8

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

 

 

Userlevel 5
Badge +40

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.

MinMax_hack_2

Userlevel 2
Badge +8

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

N @max(...

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

MinMax_hack_2

Nops its not getting through @geomancer​ image 

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))

Userlevel 2
Badge +8

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

N @max(...

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

MinMax_hack_2

Just a bit more help and understanding this may help 

 

imageimageimage

Userlevel 5
Badge +40

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

N @max(...

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

MinMax_hack_2

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.

Userlevel 2
Badge +8

@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)

 

image

Userlevel 2
Badge +8

like

 

1st Min 2ndMin

30.00 59.99

44.99 45.00

Userlevel 5
Badge +40

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.

Minimal_2_values 

Userlevel 2
Badge +8

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.

Minimal_2_values 

@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

 

 

 

 

Userlevel 5
Badge +40

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.

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

Userlevel 5
Badge +40

The workspace, for reference.

Userlevel 2
Badge +8

The workspace, for reference.

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

Userlevel 5
Badge +40

You're welcome :)

Badge

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.

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

Im testing the adress fields {1-5} and the min field ist the one i calculate

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 ?

 

Userlevel 5
Badge +40

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.

Badge

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…

 

Badge

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!

Reply