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

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.

Transpose and Join.yxmd