Solved: Rolling Rows Up To A Single Row with a Total - Qlik Community

pnn44794 · ‎2018-03-22

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.

sunny_talwar · ‎2018-03-26

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]))

View solution in original post

pnn44794 · ‎2018-03-22

Slight correction. The most recent Promise Date should be used for Promise Date and the most recent Trend Date used for Trend Date, in addition to the oldest CREATE_DATE. Sorry for any confusion.

shiveshsingh · ‎2018-03-23

Attachment is missing..

pnn44794 · ‎2018-03-23

Hello Shivesh:

Ok. That was weird. I could have sworn I had attached the files last night. Anyway, they are attached now. Thanks for letting me know.

pnn44794 · ‎2018-03-23

All:

Please ignore the requirement of Approval Complete Not Null. It is possible to have a valid row without an approval date. I just validated that. Sorry for any confusion.

tamilarasu · ‎2018-03-24

Hi Perry,

I am bit confused. The problem is that the source data file headers and expected output file headers are not matching. Also, I took one PPL# (11110) and tried to filter (By PROD_KEY3 not equal to VSI-D Clustered and not equal to Blank / Null) the data manually in excel and it shows only two rows. Could you take one example and walk me through the steps.

pnn44794 · ‎2018-03-24

Hello Tamil:

Thanks for the reply. I can understand why there's confusion. My fault. Sorry about that.

tamilarasu · ‎2018-03-26

Hi Perry,

Good morning! You explained very well. But i have a questions for you.

Question 1:

Perry Newman wrote:

As an example, I'll use PPL # 35392 (DM # in the sample data which I re-label to PPL # in the output table). If I filter PROD_KEY3 not equal to VSI-D Clustered and Not Blank (Null), plus REQUEST_TYPE like 'New%', plus COMPLETED = Null or Blank, I get 7 rows.

But the thing is I am getting 53 rows. I have attached my copy of excel file with the above filters. Could you check and let me know what I am doing wrong here.

Question 2:

I cannot then Count 7 rows as Outstanding Units, because if you look at NET ASSETS, there's 5 for each row for a total of 35. So, we need to Sum NET ASSETS and not count. I then need one row for 35392 with outstanding units of 35.

Does this make sense?

If you can answer my above question this may be makes sense . Because I am still unclear about the "Outstanding Units" sum values.

pnn44794 · ‎2018-03-26

Hello Tamil:

You are absolutely right.

My apologies again for the confusion. My fault.

tamilarasu · ‎2018-03-26

Perry - I am sorry. I can understand what you are saying but having some difficulties in checking the output. I tried something but not sure this is what you want. As I said earlier, we can take one example for testing. Could you send me the expected result for one sample PPL # (aka DM #).