community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

How to fill the next available consecutive number

Highlighted
Meteor

With the input year from 'Column B' it should find the next consecutive year available in 'Column D'.

'Column E' should be filled with the data available in 'Column C' against the year found in the above step.

 

Consider below example: Input year is 2012(Column B), the next consecutive year available in Column D is 2014(Year 2013 is missing). And column E is filled with data from Column C available against the year 2014(found in the above step)

 

Capture.JPG

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Sethu 

 

I think I understood what you need to do.

I used the R tool to check for the next consecutive year and then the Join Tool to bring respective values.

 

Check this out:

Flow123.PNG

 

The R tool code is used to bring the next consecutive year in column D. Using sapply function, I can check a single value of column B against the whole column D, and get the "minimum greater than" value (that means, the next consecutive value).

 

table = read.Alteryx("#1", mode="data.frame")
table$F <- sapply(table$B, function(x) min(ifelse(table$D > x, table$D, NA), na.rm = TRUE))
table$ F <- ifelse(is.infinite(table$F), NA, table$F)
table <- subset(table, select = -c(C,D))
write.Alteryx(table, 1)

 

Then, I used join tool with the original dataset to match respective consecutive year values. 

This brings me column E. 

 

The last thing to do is Join that column E to the original dataset again. I used left join so Null values come along (that means, if there's no consecutive year for that record, a Null value should show up).

 

See workflow and let me know if you have any doubts. Don't forget to change R code if your column names are different.

 

Cheers,

Labels