Using the Match function with numeric expressions

[#1]

For an expression in a table I am trying to use Match() to obtain an index matching a list of numeric expressions with the minimum (obtained using RangeMin).  However, the function just returns a 1 (i.e. incorrectly matches with the first item in the list).  The Help documentation talks in terms of the list containing string values, but this structure works OK with QlikView for numeric values (same data, same code). 

Alternatively, is there another function that will do the job in QlikSense?

Thanks!

[JonnyPoole]

I'd like to see the sample expression that works in QlikView if you are able to share.

[#3]

Hi Jonathan,

I'm sorry - my mistake - the issue turned out to be a bit different.  The RangeMin works correctly.  It was selecting the first item because the others were null.

The reason that they others were null is puzzling, though. They are generated by reference to a FieldValue function and it appears that this only works correctly if all the rows in the referenced field have different values.  So if. for example, the values for the field are

R1  0.5

R2  0,2

R3  0.3

R4  0.5

then when you use FieldValue('My field', 4), you get a null!

Any suggestions?

Thanks,

Leo

[JonnyPoole]

I tested out this use case .  I split the data into 2 fields so that F1 is a disinct field and F2 is not (the 4th row has a repeating value)

LOAD * INLINE [

    F1, F2

    R1, .5

    R2, .2

    R3, .3

    R4, .5

];

in both qlikview and qlik sense FieldValue('F2',4) returned a null value.  Same behavior in both products.

I check the documentation of both products and noticed the circled area below in the qlikview documentation.

Is that not whay you see ?

[#5]

That's interesting, because it works in the QV version of my app.  It turns out that if you load the data as an INUTFIELD, it reads the values via a FieldValue correctly!  It doesn't work if you do the same thing with Sense - you get zeros instead of nulls.

However, the app still does not calculate correctly, giving mostly correct values, with a smattering of incorrect ones. I attached the QV app and a spreadsheet with the data inputs and a calculation showing what the results ought to be and comparing them with what the app does with an earlier question on the QV app development forum. 

I have two results tables, one with two dimensions, Name and Month and the other with just Month.  The bigger table calculates all the values correctly, but the smaller one doesn't. The point where things appear to go wrong is where the additional aggregation for the smaller table is applied:

   rangemax(sum(total <Name> [Monthly usage]-FieldValue('Inclusive units',1)),0)

versus:

   rangemax(sum([Monthly usage])-FieldValue('Inclusive units',1),0).

Any idea what might be going on?

Thanks,

Leo

[JonnyPoole]

can u reattach ?  the files didn't come through.

[#7]

There doesn't seem to be the option to do that at this point, but the earlier thread is here: http://community.qlik.com/thread/154454

[JonnyPoole]

k -  you should be able to attach files to later posts of the same thread.  For example, if you click the link below (so that you are looking at the full thread) , hit 'reply' , you will see 'advanced editor' in the top right of the reply ... if you click that you can attach files.   just fyi for next time.

Using the Match function with numeric expressions

[#9]

K, thanks!

[#10]

Here are the QV and Excel files.