topic Re: Comparing values and choosing the smaller value? in QlikView

Comparing values and choosing the smaller value?

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

Hello guys,

So I have following Problem: i Need qlikview to look at two different rows from my data source and compare the values within. For further calculations in qlikview I Need it to find out the smaller number and hold it as a value for for example a new variable.

I apreciate every small hint, tip and help

If so far something isnt clear enough to you please just ask me to clarify

Kind regards,

Max

Re: Comparing values and choosing the smaller value?

YoussefBelloum — Mon, 02 Jul 2018 12:24:50 GMT

Hi,

for your first request: "Need qlikview to look at two different rows from my data source and compare the values within"

what are the rows that you want to look into ?

for your second request: "I Need it to find out the smaller number and hold it as a value for for example a new variable."

where want to do it ? on the script or on the front end ?

Re: Comparing values and choosing the smaller value?

vishsaggi — Mon, 02 Jul 2018 12:24:53 GMT

Can you share some sample data and your expected output please?

Re: Comparing values and choosing the smaller value?

Anonymous — Mon, 02 Jul 2018 12:30:30 GMT

Hello Youssef,

Thank you very much for your fast Reply.

1.) These two rows are within my LOAD Document, which is an Excel table

2. I dont really care where I can implement it, as Long as it works it doesnt matter. However, it maybe usefull to know that the data source im using is significantly large and that a filter machanism is up front to filter the needed rows from the selected peer.

Thank you very much

Kind regards, max

Re: Comparing values and choosing the smaller value?

Anonymous — Mon, 02 Jul 2018 12:32:48 GMT

First of all thank you for your fast reply

Im not allowed to give out any data, however, I can give you a Simulation:

so the data source are 2 rows from an Excel sheet with numbers in it looking as following: 620 or -450 ore what so ever.

in this example case the Output would Need to look like -450 however, I would also Need to convert the Output into a positive number and the final Output would be 450 in this case

Kind regards,

max

Re: Comparing values and choosing the smaller value?

YoussefBelloum — Mon, 02 Jul 2018 12:36:52 GMT

so maybe you mean: Need qlikview to look at two different COLUMNS from my data source and compare the values within ?

would you be able to describe the expected output after the comparison ?

Re: Comparing values and choosing the smaller value?

Anonymous — Mon, 02 Jul 2018 12:42:00 GMT

Yes excuse me, I mixed Things up.

so the Input data would look for example like

300 / 45

-250 / 2000

-634 / -4

...

and the Output would Need to look like:

-250

-634

I Need it to Hand out the smaller number or if there is no number smaller than 0, a Zero as Output

after this I Need to turn the Output positive, what should be possible with a FABS(x) Formula

Re: Comparing values and choosing the smaller value?

niclaz79 — Mon, 02 Jul 2018 12:46:08 GMT

RangeMin(column1,column2)

Re: Comparing values and choosing the smaller value?

YoussefBelloum — Mon, 02 Jul 2018 12:52:23 GMT

try this on the script:

table:

LOAD * Inline [

column1,column2

300,45

-250,2000

-634,-4

];

table1:

LOAD

if(column1>=0 and column2>=0,0, if(column1<column2,column1,if(column2<column1, column2))) as final

Resident table;

PFA

Re: Comparing values and choosing the smaller value?

BalaBhaskar_Qlik — Mon, 02 Jul 2018 12:57:41 GMT

May be like this:

Input:

Load

ID,

ID1

from ABC;

Noconcatenate

Temp:

Load

if(Previous(ID) < ID, num(fabs(Previous(ID)),(#,##.0)),num(fabs(ID),(#,##.0))) as IDNew,

if(Previous(ID1) < ID1, num(fabs(Previous(ID1)),(#,##.0)),num(fabs(ID1),(#,##.0))) as ID1New

resident Input;

Drop table Input;

In Expressions:

num(Previous(ID1),#,##.0;(#,##.0)).

Re: Comparing values and choosing the smaller value?

Anonymous — Mon, 02 Jul 2018 12:58:53 GMT

Thank you very much will I be able to enter that into my script or can I only use it as a formula ?

Kind regards

Re: Comparing values and choosing the smaller value?

Anonymous — Mon, 02 Jul 2018 12:59:44 GMT

thank you very much, I will try it and give you Feedback

Kind regards

Re: Comparing values and choosing the smaller value?

niclaz79 — Mon, 02 Jul 2018 13:22:26 GMT

It works in both scripting and as expressions in your visualization.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/RangeFunctions/rangemin.htm

Re: Comparing values and choosing the smaller value?

Anonymous — Wed, 18 Jul 2018 15:08:10 GMT

Thank you very much.

However, my script was not willing to execute Rangemin, no metter how I inserted the formula.

I have now solved the Problem by taking an extra step Prior to qlikview in Excel and it works perfectly fine now.

Thank you very much for your Help

best regards,

Max

Re: Comparing values and choosing the smaller value?

sunny_talwar — Wed, 18 Jul 2018 15:11:43 GMT

You can make it simpler using RangeMin....

table:

LOAD *,

RangeMin(column1, column2, 0) as RangeMin;

LOAD * Inline [

column1,column2

300,45

-250,2000

-634,-4

];

Re: Comparing values and choosing the smaller value?

YoussefBelloum — Wed, 18 Jul 2018 15:13:50 GMT

Re: Comparing values and choosing the smaller value?

Anonymous — Wed, 18 Jul 2018 15:33:00 GMT

Yes I have heard that a few times now guys, thank you for your nice help´. However, the data I am using is way bigger and more complex than the sample data I gave you guys and I have to work with multiple Variables in my formula. I guess this was why the Rangemin wasnt working out for me, since Im no expert who can make it work no matter what.

However thank you very much guys,

Best regards

Max

Re: Comparing values and choosing the smaller value?

agomes1971 — Mon, 30 Jul 2018 16:46:00 GMT

Hi,

so now you can close this thread.

Thks

André Gomes