topic Re: Want to check each numeric column for each row of test and then assign a value (yes or No) in Discussions

Want to check each numeric column for each row of test and then assign a value (yes or No)

samsam — Fri, 09 Jun 2023 23:42:28 GMT

Hi,

I want to go through each row in this table for compare value 1 thru value 8, and if one value is less than 1 and any other remaining value is greater than 1, then i say yes or no in the result column. How do i do that?

ID	value 1	value 2	value 3	value 4	value 5	value 6	value 7	value 8	Result
3200110503	0.064064	0	0	0	0	0	0	0	Yes
3200110520	0.057864	0	0	0	0	0	0	0	No
3200110546	0.06985	0	0	0	0	0	0	0
3200110562	0	0.064931	0	0	0	0	0	0
3200110597	0.06241	0	0	0	0	0	0	0

Re: Want to check each numeric column for each row of test and then assign a value (yes or No)

txnelson — Thu, 05 Nov 2020 02:15:29 GMT

In you example data table, all data are less than 1, there are no numeric data greater than 1, so the algorithm you have described could never happen. I assume that either the example data table is in error, or your description of what you are looking for is not correct.
You show that row 1 should result in a Yes, and row 2 in a No, but they are identical in data pattern. All values are less than 1 and no values are greater than 1.
I am sure that I am missing something.

Re: Want to check each numeric column for each row of test and then assign a value (yes or No)

Thierry_S — Thu, 05 Nov 2020 06:37:33 GMT

Hi,
When you are referring to "one value is less than 1" do you mean less than the numerical value = 1 or are you referring to the value of Column 1? Assuming the latter, it is still difficult to grasp the logic behind the results you shared for row 1 and row 2 as @txnelson mentioned
Please, help us help you.
Best,
TS

Re: Want to check each numeric column for each row of test and then assign a value (yes or No)

samsam — Thu, 05 Nov 2020 18:00:16 GMT

Hi,

My table is long and there are some columns for each row that has value greater than 0 and less than 1. Then next column for the same row is greater than 1.

So I want my logic as follow:

1. Check each column to see the values are greater than 0 and less than 1.

2. Second, check each column again to see if the values are greater than 1.

3. Third, if there are two columns, one represent between 0 and 1 and any other column that represents greater than 1, I fill in the result column as true.

Please let me know if that makes sense.

Re: Want to check each numeric column for each row of test and then assign a value (yes or No)

samsam — Thu, 05 Nov 2020 18:03:31 GMT

D value 1 value 2 value 3 value 4 value 5 value 6 value 7 value 8 Result
3200110503 0.064064 0 0 0 0 0 1.5 0 Yes
3200110520 0.057864 0 0 0 0 0 0 0 No
3200110546 0.06985 0 0 0 0 0 0 2.6 Yes
3200110562 0 0.064931 0 0 0 0 0 0
3200110597 0.06241 0 0 0 0 0 0 0

I am referring to check each column for each row to look for two conditions:
1. Check each column to identify any column has value greater than 0 and less than1.
2. Check each column again to identify if any column has value greater than 1.
3. If statement 1 and statement 2 is true for any row, then i report value Yes. If false, then i report value No.

Thanks again for helping.

Re: Want to check each numeric column for each row of test and then assign a value (yes or No)

txnelson — Thu, 05 Nov 2020 22:49:39 GMT

Here is a hardcoded straight forward piece of formula that will create the results you want, given your latest description

If(
	(:value 1 > 0 & :value 1 < 1 | :value 2 > 0 & :value 2 < 1 | :value 3 > 0 &
	:value 3 < 1 | :value 4 > 0 & :value 4 < 1 | :value 5 > 0 & :value 5 < 1 |
	:value 6 > 0 & :value 6 < 1 | :value 7 > 0 & :value 7 < 1 | :value 8 > 0 &
	:value 8 < 1) & Max(
		:value 1,
		:value 2,
		:value 3,
		:value 4,
		:value 5,
		:value 6,
		:value 7,
		:value 8
	) > 1,
	"Yes",
	"No"
)