topic Re: Logic help to find nearest upper and lower value from two data sets. in QlikView

Logic help to find nearest upper and lower value from two data sets.

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

Hi,

In given data, I have two data set. 1) Source data 2) Lower & Upper Limit Data.

I want Upper point and lower point data from " Lower & Upper Limit Data." to "Source data" as new Column against Pressure and temperature.

Please help me out to write the logic.

Thanks,

Lalit

Re: Logic help to find nearest upper and lower value from two data sets.

vishsaggi — Thu, 31 May 2018 17:11:42 GMT

In your expected output, can you tell the logic how you got the values for Upper Pressure, Lower Pressure, Lower Temp and Upper Temp? At least one example row how you got the output?

Re: Logic help to find nearest upper and lower value from two data sets.

lalitkgehlot89 — Thu, 31 May 2018 17:33:17 GMT

Hi Vishwarath,

"Pressure Mpa" of Source data need to matched with "Pressure" of "Lower & Upper Limit" and same for "Temperature C" with "Temperature".

Suppose I have value 0.01 in "Pressure Mpa" and same value I have in Pressure then both Upper and Lower Pressure is same for Output data. If data is not matched in that condition Just small value from "Pressure" and just large value from "Pressure" will be the lower and upper Pressure value for "Pressure Mpa" as an output.

For Lower and Upper Temp it will also check the "Pressure Mpa" value. and other condition will be same.

example :- for 0.15 there is no equal value in "Pressure". now in output lower is 0.1 and upper 0.2.

for 725 there is no equal value in "Temperature", now 700 and 800 will be the upper and lower temp.

Re: Logic help to find nearest upper and lower value from two data sets.

vishsaggi — Fri, 01 Jun 2018 02:43:49 GMT

Where did you pick 0.1 and 0.2 i mean from which fieldname? And how you got 700 and 800 for 725 temp?

Re: Logic help to find nearest upper and lower value from two data sets.

lalitkgehlot89 — Fri, 01 Jun 2018 03:36:59 GMT

Hi Vishwarath,

0.1 or 0.2 is from Pressure field of "Lower & Upper Limit" sheet of Attached Excel. because 0.15 not present in table.

in this case just small value is 0.1 and just large value is 0.2.

Now on the basis of 0.1 and 725. I check the same for Temp in Temperature filed of "Lower & Upper Limit".

I got 700 smaller and 800 larger value for 725 with 0.1 Pressure.

Re: Logic help to find nearest upper and lower value from two data sets.

qlikviewwizard — Fri, 01 Jun 2018 03:56:39 GMT

What are the logics to show output for Upper Presser, Lower Temperature, Upper Temprature?

Re: Logic help to find nearest upper and lower value from two data sets.

lalitkgehlot89 — Fri, 01 Jun 2018 04:22:03 GMT

In Attached file,

I have two set of data 1) Source data 2) Lower & Upper Limit data.

I want Lower & Upper Limit data sheet field Temperature & Pressure data as a upper & Lower Temperature and upper & Lower Pressure with the Source data.

Logic :- 1) First compare "Pressure Mpa" with Pressure if matched then Pressure will be lower Pressure and upper Pressure,

If Not Matched in that condition just smaller value of Pressure will be lower Pressure and Just Larger value of Pressure will be upper Pressure,

Example :- 0.1 exists in both data set so 0.1 is lower Pressure and 0.1 is upper Pressure. but 0.15 not exits in my 2nd data set (Lower & Upper Limit data.). Just Smaller value 0.15 is 0.1 and just upper value is 0.2. here 0.1 is lower Pressure and 0.2 is upper Pressure.

2) Now for Temperature C condition will be the but it will check Pressure.