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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

LOOP

Highlighted
Meteoroid

Apps CASE_YEAR MI017 MI2018 MI_COMB
1         2017             100     105         100
2        2017              50         51          50
3        2018              35          40         40
4        2017              120       150        120
5        2018               45          46         45

 

Hi All - I need to calculate the last column based on the case_year column. I can use the formula tool to with IF conditions to do that.

Can we use iterative loop instead?

 

Pulsar

Hi @UmarS 

 

Would you please explain the logic needed for your calculation?

 

Thanks,

Esther

Nebula

Hi @UmarS 

 

It looks like the MI_COMB columns takes that value from either MI2017(I assume its a typo) or MI2018 based on Case_Year, i.e. use MI2017 if CASE_YEAR is 2017, etc.  If that's the case shouldn't the MI_COMB for row 5 be 46, instead of 45?

 

Dan

Meteoroid

Logic = If Case _Year =2017: 2018 then pick the correct value from MI2017:MI2018 columns and populate the MI_Comb column.

Meteoroid

Yes sorry. Its a typo. The value should be 46

Nebula

Hi @UmarS 

 

A simple formula will produce the output you're looking for

 

results.png

 

 

If the columns can change, you'll need to have dynamic solution that can adapt to this.  If this is case, let us know and we'll see what we can come up with

 

Dan

Meteoroid

Thanks Dan. Yes simple loop works but I am looking for a solution to handle dynamic columns that can change. I am wondering if iterative/batch  macros can be used instead

Nebula

Hi @UmarS 

 

Can you provide some samples with different rows/columns that we can look at.  With a clever use of transpose/crosstab you may not need a macro at all. 

 

Dan

Nebula

@UmarS 

 

Here's dynamic version that uses a transpose/filter combination to determine the MI_COMP column.

 

w.png

 

The transpose tool pivots the data so all the year information is is 2 columns, Name and Value.  The filter selects only the rows where the Case_Year is in the Name field.  The Value field is renamed to MI_COMP and then joined back to the original data.  The join is on App and case_year, so you can find the data for multiple years for each app, as in the 1st 2 rows below

 

R.png

 

I added an extra data to your input to demonstrate the dynamic nature of the solution.  It should work with any number of rows and columns

 

Dan

Meteoroid

Dan - Thanks yes this works. Is there a way to solve this problem through macros?

Labels