topic Re: Rolling Rows Up To A Single Row with a Total in QlikView

Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:21:05 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 23 Mar 2018 01:35:24 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

shiveshsingh — Fri, 23 Mar 2018 04:05:27 GMT

Attachment is missing..

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 23 Mar 2018 13:34:42 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 23 Mar 2018 14:53:01 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

tamilarasu — Sat, 24 Mar 2018 07:59:29 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:23:44 GMT

Hello Tamil:

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

Re: Rolling Rows Up To A Single Row with a Total

tamilarasu — Mon, 26 Mar 2018 07:27:47 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:24:39 GMT

Hello Tamil:

You are absolutely right.

My apologies again for the confusion. My fault.

Re: Rolling Rows Up To A Single Row with a Total

tamilarasu — Mon, 26 Mar 2018 16:19:06 GMT

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 #).

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:25:55 GMT

Hello Tamil:

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

Re: Rolling Rows Up To A Single Row with a Total

sunny_talwar — Mon, 26 Mar 2018 18:53:17 GMT

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

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:26:37 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

sunny_talwar — Mon, 26 Mar 2018 19:43:35 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Mon, 26 Mar 2018 20:22:40 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

tamilarasu — Tue, 27 Mar 2018 01:55:18 GMT

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!!!

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:27:55 GMT

Hello Sunny:

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

Re: Rolling Rows Up To A Single Row with a Total

sunny_talwar — Tue, 27 Mar 2018 17:43:08 GMT

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

Re: Rolling Rows Up To A Single Row with a Total

pnn44794 — Fri, 11 Oct 2019 22:28:58 GMT

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.

Re: Rolling Rows Up To A Single Row with a Total

sunny_talwar — Thu, 29 Mar 2018 00:31:03 GMT

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