How to run the same code on different columns in the same workflow

[Pankhudri20]

Hello,

I have to flag warnings for certain conditions for the attached sample data.

For example: issue warning "if C1<1 lasts 1 hour".

However, there are C1....C11 column values in the data.

I calculated running sum total for the rows where C1 < 1 and will check for time equal to or greater than 60 minutes. 

Can you please tell me how to repeat the same process for other columns optimally?

Please find the sample data and workflow attached for reference.

Thank you

Pankhudri

[Pingu]

If you want do do all columns at once with one formula you probably need to use the transpose tool.

I believe this challenge very much describes what you need (you can look at peoples solutions):

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-271-Dynamic-Double-Dip/td-p/769252

So you could create a lot of different formulas or do it all at once with a transpose/crosstab before the formula tool.

[Pankhudri20]

Hello,

Thank you for your quick response.

I have attached the sample data excel file if you want to check my workflow.

I will look at the solutions in the link attached.

Regards,

Pankhudri

[jgv27]

Hi @Pankhudri20,

I had a look at your question and think I over-engineered a workflow for your purposes (after looking at what @Pingu proposed).

You are more than welcome to take a look at the workflow (batch macro) that I've put together. It is however not optimal, as I can not get it to batch all the "C"-columns only once. At the moment it works through an average option in the summarize tool, and can definitely be optimized further.

All the best with your workflow! 

[Pankhudri20]

Hello @jgv27 

Thank you for your response.

I am unable to download your workflow. 

@Pingu 

I have a very large dataset. I tried transposing but it did not work. Can you tell me how to make this work?

Regards,

Pankhudri 

[Pingu]

Hi @Pankhudri20  ,

I have used your workflow as an example to do the same thing for all columns.

I used transpose and then crosstab to put it back in your format.

Is this what you need?

[Pankhudri20]

Hello @Pingu 

Thank you so much for your response.

The transpose worked from your workflow.

However, Running Sum Demand is repeating same value for some columns instead of increasing every row.

Can you tell me what logic you applied for calculating that?

Regards,

Pankhudri

[Pingu]

I copied your logic and used it in a multi row formula as you can see in the tool:

if [Value]=[Row-1:Value] then [Value]+[Row-1:RunningSumDemand]
else [Value] endif

I think it errors then because with this logic it will only work if all the 1's are after each other like in the example. So it should not compare with previous row but just if the value is if a 0 or 1. And if its a 1 then do +1 for the RunningSumdemand field. If that makes sense.