Want to check if there is value present in the range mentioned in another two columns.

I have a excel file which contains a data like the below-

ID	Range1	Range2	Is Aligned or not	L00	L01	L02	L03	L04	L05
111T	L00	L05	YES	0	5	10	0	0	1
A112T	L00	L02	NO	0	0	0	0	50	0
113T	L02	L04	YES	0	0	9	0	0	0
4T5	L04	L04	NO	0	0	0	7	0	0
178	L03	L05	YES	0	0	0	5	4	9
15Y	L00	L00	YES	1	5	0	0	0	0
190	L01	L03	NO	10	0	0	0	0	0

I want to fill column 'Is Aligned or not' based on a condition that I want to check if there is any value present between the range mentioned in column 2 and 3, so for example here for the 1st row of data i.e. ID as 111T the range would be L00 to L05, so I need the Alteryx code to check if there is any value (greater than zero) is present between L00 to L05 i.e. it needs to check L00,L01,L02,L03,L04,L05. So as there is a value present in L01 and L02 as well, I entered YES in column 4.

But when we check for ID as A112T, the range is L00 to L02, so the Alteryx needs to check if there is any value that is greater than zero in L00 or L01 or L02 and as there are no value present other than zero, I entered the value of column 4 as NO.

And if there is a value present which is outside the range mentioned in column 2 and 3, then also the value of column 4 should be NO, as that is happening for ID 4T5 and 190

So I need help in deciding how to check or select a range and solve the whole problem.

P.S - I have added on 5 ranges but in real I have around 100 ranges i.e. L00 to around L99

Any help would be highly appreciated.

Custom Tools

Macros

Transformation

Dynamic Processing

Optimization

Data Investigation

Accepted answers

Christina_H

Here's an example of the technique for your test data. I joined back to the original data rather than using a cross-tab, but it's the same principle as suggested above.

Range check.yxmd

All comments

mbarone

As with all things Alteryx, there are probably a few different ways to handle this. I would personally prefer a Transpose tool and then a Formula tool, followed by a Crosstab tool (the links are from the Tool Mastery Index - you should familiarize yourself with it).

For the Transpose tool, you'll want to group by your first 3 columns, and then transpose all other columns. Then you can use a formula tool to compare each row's "Name" value and "Value" value to Range1/Range2 and update the Is Aligned column as applicable. Then use a Crosstab tool to flip the data back to it's original form.

Play around with it and let us know if you have questions as you build it. It would be a great exercise to get you used to those 3 tools, which are used a lot (at least for me they are).

Cheers!

swarajjoshi

Hello @mbarone , thanks for your suggestion.

But that's what I did earlier, but when it compares 'Name' and 'Value' it does that for only those values which are present in column Range1 and Range2 and not between the whole range.

For e.g, the code compares for "Name" and "Value" for L00 or L05 and not that those in between, so it says NO but instead should have answered YES as there are value L01,L02,L03,L04

But thanks for taking your time and looking into this.

mbarone

Right, you'd have to develop a proper formula to do that. Something like this ( just did it off the top of my head - might need some tweaking, but you'll get the idea):

IIF
(
ToNumber(Substring([Name],1,99))>=ToNumber(Substring([Range1],1,99))
AND
ToNumber(Substring([Name],1,99))<=ToNumber(Substring([Range2],1,99))
AND
[Value]>0,
'Yes',
'No'
)

Christina_H

Here's an example of the technique for your test data. I joined back to the original data rather than using a cross-tab, but it's the same principle as suggested above.

Range check.yxmd

swarajjoshi

Hello @Christina_H Thanks so much for your help, the solution is working absolutely fine. And also a special mention to @mbarone for guiding us towards the end solution.

mbarone

Welcome!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703