topic Re: Using the Match function with numeric expressions in App Development

Using the Match function with numeric expressions

Tue, 03 Mar 2015 14:18:17 GMT

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!

Re: Using the Match function with numeric expressions

JonnyPoole — Tue, 03 Mar 2015 15:16:10 GMT

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

Re: Using the Match function with numeric expressions

Tue, 03 Mar 2015 15:59:06 GMT

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

Re: Using the Match function with numeric expressions

JonnyPoole — Tue, 03 Mar 2015 16:34:38 GMT

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 ?

Re: Using the Match function with numeric expressions

Tue, 03 Mar 2015 20:18:48 GMT

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

Re: Using the Match function with numeric expressions

JonnyPoole — Tue, 03 Mar 2015 21:09:46 GMT

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

Re: Using the Match function with numeric expressions

Tue, 03 Mar 2015 21:22:32 GMT

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

Re: Using the Match function with numeric expressions

JonnyPoole — Tue, 03 Mar 2015 21:34:13 GMT

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

Re: Using the Match function with numeric expressions

Tue, 03 Mar 2015 21:46:20 GMT

K, thanks!

Re: Using the Match function with numeric expressions

Thu, 05 Mar 2015 15:49:41 GMT

Here are the QV and Excel files.

Re: Using the Match function with numeric expressions

JonnyPoole — Fri, 06 Mar 2015 18:59:32 GMT

hi - i read your post and looked at the app but it wasn't obvious to me what the issue was , even once i tracked down the expression you had referenced

Basde on the expression formula you typed above, i believe you are directly referring to 'OV-T1' expression in large and small charts on the 'outputs' tab.

Is that correct ?

I did not see a difference in the totals ? Can you help explain ? or does this give you different results on your machine ?

Re: Using the Match function with numeric expressions

rwunderlich — Fri, 06 Mar 2015 20:00:30 GMT

FieldValue() returns the nth value of distinct values for that field. It does not return a value from the "nth" row. Given only 3 possible values, it's expected that

FieldValue('My field', 4),

would return null.

-Rob

Re: Using the Match function with numeric expressions

rwunderlich — Fri, 06 Mar 2015 20:10:23 GMT

If you want to pull values from the 4th row for your T4 expression, you can use a reference to unique row value like [Tarrif Name]

only({<[Tariff Name]={Four}>}[Rental per month])+

RangeMax(sum([Monthly usage])- only({<[Tariff Name]={Four}>}[Inclusive units]),0) *

only({<[Tariff Name]={Four}>}[Cost per non-inclusive unit])

-Rob

Re: Using the Match function with numeric expressions

Sun, 08 Mar 2015 21:08:35 GMT

It produces correct results for Andy for OV-T1, but not for Colin and some other results are also incorrect.

Re: Using the Match function with numeric expressions

Sun, 08 Mar 2015 22:02:42 GMT

Hi Rob,

The Only() structure appears to work well at extracting values from the Tariffs table - Thanks!

However, the problem now is that the chart calculates the *total* overage (i.e. summed across the three months) and then applies the rest of the calculation to that. What I want it to do is to do the calculation month by month and then to sum the results..

I guess that this will require an aggregation function ( Aggr() ?), but I can't seem to get this to work.

Thanks,

Leo