Issues with Summarizing multiple fields at once

[Ramon_dup_257]

I am trying to Aggregate multiple rows using the Summarize tool.

My data looks like this :

P1	M1	D0
P1	M1	D1	D2
P1	M1			D3

I want to group by P1 and M1 and want to take the last not-null value in the columns containing D1, D2 and D3.

When I use the summarize tool, I group on P1 and M1 and pick the last value for each of the D-columns.

What I want is :

P1

M1

D1

D2

D3

What I am getting is :

P1	M1			D3

I think what might be happening is that the summarize tool is picking up the last value of the other D-columns as null and giving me the result accordingly.

Also, the D-fields are string fields and thus I cannot use the Max operation.

Is there a way to pick the last non-null value for the columns ?

I understand that I can summarize the individual columns and join them with the rest of the columns but it does not seem like a good approach to me as I have a sufficiently large number of columns to run the operation on.

Thanks.

[MarqueeCrew]

A simple answer might be:

Groupby P1

Groupby M1

MAX D1

MAX D2

MAX D3

Is that a valid solution?

[Ramon_dup_257]

I forgot to include that the columns are String fields and thus the max operation won't work.

I edited my original post to reflect this.

Apologies.

[MarqueeCrew]

I've created a module that uses a mult-row formula (3) to perpetuate the last value.

I hope that this will help to solve your problem.

Thanks,

Mark

[Ramon_dup_257]

This was also the solution that I had in mind

However, I will have to put in about 7 Multi-Row Formula for my purpose.

Is there a way to use the a Multi-Row Formula across multiple fields as well ?

Until then, this was the best answer.

Thanks!

[MarqueeCrew · Accepted solution]

Here's a version that is more dynamic for you:

This might be the better answer that you were hoping for?

Thanks,

Mark

[Ramon_dup_257]

This works exactly as I expected!

Thank you so much!

[Ramon_dup_257]

@MarquuCrew, I realised that my problem is a little more layered than I previously anticipated. I have two attribute fields (A and B, non-numeric) for each of the Fields F1 through F7. which I need to populate as well.

Your solution while correct for the previous half of the problem will only pick the last values for A and B in the P1-M1 combo.

My table structure currently is  :

The output should be like :

Do you have any suggestions for leads on this problem ?

[MarqueeCrew · Accepted solution]

@Ramon_dup_257, If you modify the solution to include a record id after you read the input & put that record ID as a KEY field into the transpose....

That should solve the issue.  I've included a sample module for you (version 3).  Please try this yourself before you look at the solution.  I tested with your single record example and got the desired result.

Thanks,

Mark