Issues with Summarizing multiple fields at once

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 :

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.

Best Practices

Transformation

Accepted answers

MarqueeCrew

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

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

Thanks,

Mark

Aggregate Multiple Rows v2.yxmd

MarqueeCrew

@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.