Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

swarajjoshi
7 - Meteor

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

 

IDRange1Range2Is Aligned or notL00L01L02L03L04L05
111TL00L05

YES

0510001
A112TL00L02NO0000500
113TL02L04YES009000
4T5L04L04NO000700
178L03L05YES000549
15YL00L00YES150000
190L01L03NO1000000

 

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.

 

6 REPLIES 6
mbarone
16 - Nebula
16 - Nebula

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
7 - Meteor

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
16 - Nebula
16 - Nebula

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
14 - Magnetar

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.

Christina_Hurrell_0-1636554446762.png

 

swarajjoshi
7 - Meteor

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
16 - Nebula
16 - Nebula

Welcome!

 

Labels