Rolling Rows Up To A Single Row with a Total

[pnn44794]

Hello:

I need to be able to roll up Orders, a single order can have multiple rows, into one row per order with a total sum amount in a straight table.  Net Assets should be summed or it might be best to count ORDERS IN (I'm not sure).  In other words, if Order 1 has 10 rows with a total sum of 15, it needs to be consolidated down to one row with a total of 15 for Outstanding Units.  I've attached a sample output format.  I should wind up with ~40 - 70 rows of data.

•  

As always, thanks in advance for any and all replies.

[pnn44794]

Hello Tamil:

No problem whatsoever.  I'm happy you're trying to help me.

[sunny_talwar · Accepted solution]

I used tamilarasu‌'s attachment to make modifications to get this

Added a new flag field in the script... to avoid the if statement

If((PROD_KEY3 <> 'VSI-D' and Len(Trim(PROD_KEY3))>0) and REQUEST_TYPE Like 'New*' and Len(Trim(COMPLETED))=0, 1, 0) as Flag

And with these dimensions/expressions

Dimensions

CIO / LOB

PPl#

ORDERS IN

Expressions

Sum({<Flag = {1}>}[NET ASSETS])

Date(Min({<Flag = {1}>}[CREATED DATE]))

Date(Min({<Flag = {1}>}[Promise Date]))

Date(Max({<Flag = {1}>}[Trend Date]))

[pnn44794]

Hello Sunny:

Thanks for the reply.  I'm pretty confident your solution will work, but I have another problem and perhaps I should have mentioned this.

[sunny_talwar]

You can use mapping load with applymap or joins... or you can fall back to the if statement or set analysis on the front end..... you also have the option to do some of the things in the script and others on the front end... it all depends what is possible and what isn't.

[pnn44794]

Thanks Sunny.  For the moment, I'm going to fall back to the if statement within the front end as this is not a heavy duty, data laden app.  However, I like your approach and will look to do, probably, a join down the road as this app grows.  I think I'll need to.

Again and as always, thank you for your help.

[tamilarasu]

Perry - Sorry for the delayed response. I was waiting for your reply but it was time for me to sleep. Thought of answering you in the morning. But I am glad that stalwar1‌‌ (24/7) has answered all your questions and suggested the best solution. I was not able to help you this time but I will try my level best next time (if I see your name). Have a fantastic day, Guys!!!

[pnn44794]

Hello Sunny:

I've tried doing an outer join (want all rows from both tables returned) which appears to work.

[sunny_talwar]

You need to do this in a resident load after the join has been performed... PROD_KEY3 isn't available while doing the join

[pnn44794]

Hello Sunny:

I'm getting an error when trying to do a resident load.  I think I'm very close to implementing your solution, but I obviously have something wrong.

[sunny_talwar]

The attached log file ran without any issues... the only problem is synthetic keys...