topic Re: Show higher values AND smaller than current, using different columns in QlikView

Show higher values AND smaller than current, using different columns

luiz_damascena — Wed, 25 Nov 2020 16:16:04 GMT

Imagine the following scenario: a set of records as below

Field1, Field2, Field3, Field4

1, 2, 3, 4

5, 6, 7, 8

9, 10, 11, 12

13, 14, 15, 16

17, 18, 19, 20

With the applied filters, the information visualized is from the third line: 9,10,11,12

I want to know, among all the values in my recordset,

What is the next value down from the Field 3 column. The answer would be 7.
What is the next value up from the Field 4 column. The answer would be 16.

Answers will be displayed in the same query, without changing my current filtering.

It is as if I wanted to display the current value and also the maximum value using the Max() function.

But I want to display the next higher or lower value for different columns, as the case may be.

Is it possible?

Thank you in advance,

Luiz Carlos.

Re: Show higher values AND smaller than current, using different columns

sunny_talwar — Thu, 26 Apr 2018 17:48:19 GMT

Will you be elaborate on where exactly are you hoping to see 7 and 16? And it will be always Field 3 down and Field 4 up? or is there another way user are able to determine this?

Re: Show higher values AND smaller than current, using different columns

Anonymous — Thu, 26 Apr 2018 17:53:38 GMT

Hi,

Something like this

Regards,

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 12:38:24 GMT

I'll try to make the question clearer ...

Let us assume that Field1 is the product code (a car, for example), Field2 is a month, Field3 is the average consumption, Field4 is the highest speed achieved, Field5 is the longest distance traveled, etc.

That is, each field after Field2 are measured quantities relative to my car, in a given month.

Looking at the information of car 1 in March, I also want to see what the average consumption is closer to mine, up and down; at the same time, I want to know the highest speed, up and down, always considering all the cars in the same month.

I can not use classification. Assuming a possible index using Field1, the desired information (in relation to my average consumption, which is immediately below mine?) will not be in the "top line". It can be anywhere, in any line.

I return to the example of the maximum and minimum: displaying my speed, to know the highest and lowest speeds recorded, considering all vehicles, it would be enough to use max () and min ().

But I do not want the maximum value: I want the value closest to mine, which can be in any line.

I hope I have elaborated better ...

Thank you in advance.

Luiz Carlos

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 12:39:41 GMT

I tried to elaborate a better question... please, read it.

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 12:41:32 GMT

Interesting approach, Mauri, but not exactly what I want. But thank you! I tried to elaborate my question better... please, read it.

Re: Show higher values AND smaller than current, using different columns

sunny_talwar — Fri, 27 Apr 2018 12:52:48 GMT

That is, each field after Field2 are measured quantities relative to my car, in a given month.

So, your car is not part of the data because everything is relative to your car?

Looking at the information of car 1 in March

Now car 1 is your car? but this is not part of the database or is it?

I am feeling lost, would you be able to share a sample or sample data and give out exactly what you are hoping to get out of it?

Re: Show higher values AND smaller than current, using different columns

sasiparupudi1 — Fri, 27 Apr 2018 13:08:46 GMT

Please attach a sample

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 13:22:45 GMT

Right now, grateful for your attention and patience ...

"My car" is part of the data. Each car in the database has its own information.

Here's a sample data:

Fields

carCode, monitoredMonth, averageConsumption, highestSpeed, distanceTraveled

Values

1, Jan, 14.2, 100, 240

2, Jan, 7.7, 85, 400

3, Jan, 12.1, 120, 200

4, Jan, 8.5, 92, 130

5, Jan, 10.1, 70, 350

As extra information, the units of measure of the columns: averageConsumption km / l, highestSpeed km / h, distanceTraveled km.

Seeing information from car 3, I see that averageConsumption is 12.1 km / l, highestSpeed 120 km / h, distanceTraveled 200 km.

Also, I want to display the averageConsumption immediately lower than that of car 3, 12.1 Km / l, which will be on the screen. The answer will be 10.1 km / l (information linked to car 5)

I also want to show the distance traveled immediately greater than that of the car, 200 Km, which will be on the screen. The answer will be 240 km (information linked to car 1).

Re: Show higher values AND smaller than current, using different columns

sunny_talwar — Fri, 27 Apr 2018 14:09:59 GMT

This?

Re: Show higher values AND smaller than current, using different columns

Mark_Little — Fri, 27 Apr 2018 14:50:57 GMT

I would use peek

So something Like

ABOVE:

LOAD

ROWNO() AS Order,

FIELD1,

PEEK(FIELD,-1) as ABOVEFIELD1,

Then reload the Table is reverse order to get the below.

NOCONCATENATE

BELOW:

LOAD

Order ,

FIELD

PEEK(FIELD,-1) as BELOWFIELD1

...

Resident ABOVE

Order by Order desc;

Mark

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 14:51:27 GMT

Almost there!!!

First of all, the expression you created is scary ... lol

When you select the car, it shows the car number that meets the condition - and I want to show the value read.

For example: Selecting car 3, is shown in Closet Field A Car number 5. But I want to show the value that is in this field, for car 5. In the case, 10.1.

But I could not adjust your expression ...

Thank you in advance.

Re: Show higher values AND smaller than current, using different columns

sunny_talwar — Fri, 27 Apr 2018 14:54:10 GMT

Check attached

Changed this

Pick(Match(RangeMin(Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

Re: Show higher values AND smaller than current, using different columns

sunny_talwar — Fri, 27 Apr 2018 14:55:33 GMT

If doing this in the script is an option, i.e., this information remain static and doesn't change based on selection, I would do it the way mark6505‌ has pointed out

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 15:03:24 GMT

Perfect!

I'll check the possibility of doing it via script, but what I wanted, your expression does!

Now it is to understand more and adapt to my data model ...

But, thank you, again.

Luiz Carlos.

Re: Show higher values AND smaller than current, using different columns

sunny_talwar — Fri, 27 Apr 2018 15:11:55 GMT

Let me break it down a little

Step1 - Calculate the difference between Current Car - Value Above and Value Below

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

In this step, you can probably use set analysis instead of using Aggr() and Above() which will make your expression perform better... but if we continue with this

Step2 - find the absolute value of the difference

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

Step3 - find the min difference

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

Step4 - Find the value associated with the minimum difference...

Pick(Match(MinAbsoluteDifference, AbsoluteDifference1, AbsoluteDifference2), Value1, Value2)

So if MinAbsoluteDifference = AbsoluteDifference1, then pick Value1, if MinAbsoluteDifference = AbsoluteDifference2, then pick Value2

I hope this will make it somewhat easy to understand.

Best,

Sunny

Re: Show higher values AND smaller than current, using different columns

luiz_damascena — Fri, 27 Apr 2018 15:17:45 GMT

Thanks a lot, Sunny!