Loop over two columns

[fostc80857]

Hi

I would like to be able to loop over two columns at a time and perform some analysis on each pair of columns (which would then be appended to create a final analysis file).

The dataset would be as follows (smaller extract provided due to size of file):

TEST DATA.xlsx (9.1 KB)

So the loop would look at columns A&B first, then C&D, E&F and each analysis step would add a flag of 1 if the Level (%) was greater than 100. For each pair of columns I would like the output file to take the header from the first column (i.e. from columns A, C, E) and sum the number of flags (I know the sum will be zeros based on the limited dataset).

Thanks
Chris

[HansS]

Hi @fostc80857

Does this post help?

Column List Loop Start - two columns at a time.

I have a table that consist of columns containing pre-test and post-test data. There n pre-test columns and an equal number of post-test columns. I have the columns sorted so that pre-test item 1 is next to post-test item 1 and so on for the remaining columns. What I would like to do is select the first 2 columns do some analysis that will result in a new variable, then take the next 2 columns and do some analysis that will result in a new variable for that item. So I will end up (using the Loop…

gr. Hans

[fostc80857]

Hi @HansS

Yes, this post helps, thanks for pointing me too it.

In terms of the output file. This is what I have (based on only the first two columns:

How could I produce another output table that take the NU ref (1st column header) as a row then has a sum of spill indicator column and a sum of spill duration - so it would look like this

PLR spill count total duration
NU18131324 91 114.75

Thanks
Chris

[fostc80857]

Also, when I only have three sets of column pairs in the test data set, the “Loop End (Column Append)” node will execute. When I connect to the much larger source file which has 56 column pairs (112 columns in total), the “Loop End (Column Append)” will not execute. It just remains at the configured state.

Is there a reason for this?

[elsamuel]

You can use a GroupBy node to group the data by the NU column, and aggregate the others however you want.

[fostc80857]

Hi @elsamuel

If I use the groupby would that not just group the dates in the NU column?

What I am after is a column of the NU references with the associated spill counts and spill duration. Something like this:

Thanks
Chris

[elsamuel]

I see. I missed that in the initial post.

Maybe try an Unpviot node?

Before:

Unpivot Configuration:

Results:

Then do a GroupBy: