Check a specific field in dataset depending on the value of a string

[phil_budden]

Hi All,

I have a dataset which looks something like below (only with hundreds of fields):

PART_ID	LOCATION_TO_CHECK_1	LOCATION_TO_CHECK_2	LOCATION_TO_CHECK_3	LOCATION_A	LOCATION_B	LOCATION_C	LOCATION_D
PART_1	LOCATION_A	LOCATION_B	LOCATION_C	1	5	1	0
PART_2	LOCATION_B	LOCATION_C	LOCATION_D	3	1	0	1
PART_3	LOCATIN_C	LOCATION_D	NA	2	0	0	3

I want to replace the text in "LOCATION_TO_CHECK_X" with "Y" or "N" depending on if the number in the denoted location is >0

I assume I'm going to use an IF statement but how do I tell Alteryx to check the right field?  I can't manually tell it which location to check as my workflow to this point has populated these fields based on certain criteria so this will change when I run differnet data through it.

Any ideas welcome, thanks in advance.

[jdunkerley79 · Accepted solution]

Think the simplest way is to use a couple of transposes

- On the Location to Check fiels

- On the Location fields

Then join the two outputs of these together and use a simple formula to convert to Y/N 

Finally Cross tab back to original layout.

If you have a really dynamic number of fields this approach probably wont work as would need to be changed for each set of columns.

[phil_budden]

Thanks, I'll give this a go and see if it works

[phil_budden]

It worked

Thanks for your help